Definition of Oracle EXISTS Operator

 The Oracle EXISTS operator is a Boolean operator that return true or false. To check the existence of     rows in a table the EXISTS operator is frequently used with a subquery.

STRUCTURE:

SELECT *

  FROM table

   WHERE EXISTS

              (Subquery)

Note: If the Subquery returns any rows then the EXISTS operator returns true ,otherwise returns false.

The processing of the Subquery terminated by the EXISTS operator if the Subquery return first row.

Oracle EXISTS examples

Let’s take some examples of using EXISTS operator to see how it works.

Oracle EXISTS with SELECT statement example

See the following customers and orders tables in the sample database:

The following example uses the EXISTS operator to find all customers who have the order.

SELECT
    name
FROM
    customers a
WHERE
    EXISTS (
        SELECT
            1
        FROM
            orders
        WHERE
            customer_id = a.customer_id
    )
ORDER BY
    name;
For each customer in the customers table, the subquery checks whether the customer appears on the orders table. If yes, then the EXISTS operator returns true and stops scanning the orders table. Otherwise, the EXISTS operator returns false if the subquery does not find the customer in the orders table.
The result if the  EXISTS operator is used by the WHERE clause to retrieve the customer that makes the subquery returns any rows.
Note that Oracle ignores the select list in the subquery so you can use any column, literal value, expression, etc. In the query above, we used literal number 1.