Oracle EXISTS

Summary: in this tutorial, you will learn how to use the Oracle EXISTS operator to test for the existence of rows.

Introduction to the Oracle EXISTS operator

The Oracle EXISTS operator is a Boolean operator that returns either true or false. The EXISTS operator is often used with a subquery to test for the existence of rows:

SELECT
    *
FROM
    table_name
    WHERE
        EXISTS(subquery);
Code language: SQL (Structured Query Language) (sql)

The EXISTS operator returns true if the subquery returns any rows, otherwise, it returns false. In addition, the EXISTS operator terminates the processing of the subquery once the subquery returns the 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 c
WHERE
    EXISTS (
        SELECT
            1
        FROM
            orders
        WHERE
            customer_id = c.customer_id
    )
ORDER BY
    name;
Code language: SQL (Structured Query Language) (sql)

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 of the EXISTS operator is used by the WHERE clause to retrieve the customer that makes the subquery return 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 the literal number 1.

Oracle EXISTS with UPDATE statement example

See the following warehouses and locations tables:

The following statement updates the names of the warehouses located in the US:

UPDATE
    warehouses w
SET
    warehouse_name = warehouse_name || ', USA'
WHERE
    EXISTS (
        SELECT
            1
        FROM
            locations
        WHERE
            country_id = 'US'
            AND location_id = w.location_id
    );
Code language: SQL (Structured Query Language) (sql)

For each warehouse, the subquery checks whether its location is in the US or not. If yes, the EXISTS operator in the WHERE clause returns true that causes the outer query to append the string ', USA' to the warehouse name. Otherwise, the UPDATE statement does nothing due to the condition is the WHERE clause is false.

The following query verifies the update:

SELECT
	warehouse_name
FROM
	warehouses
INNER JOIN locations
		USING(location_id)
WHERE
	country_id = 'US';Code language: SQL (Structured Query Language) (sql)

Oracle EXISTS with INSERT statement example

Suppose, we have to send special appreciation emails to all customers who had orders in 2016. To do this, first, we create a new table to store the data of customers:

CREATE TABLE customers_2016(
    company_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    company varchar2(255) NOT NULL,
    first_name varchar2(255) NOT NULL,
    last_name varchar2(255) NOT NULL,
    email varchar2(255) NOT NULL,
    sent_email CHAR(1) DEFAULT 'N',
    PRIMARY KEY(company_id)
);
Code language: SQL (Structured Query Language) (sql)

Then, we insert customers who had orders in 2016 into the customers_2016 table:

INSERT
    INTO
        customers_2016(
            company,
            first_name,
            last_name,
            email
        ) SELECT
            name company,
            first_name,
            last_name,
            email
        FROM
            customers c
        INNER JOIN contacts ON
            contacts.customer_id = c.customer_id
        WHERE
            EXISTS(
                SELECT
                    *
                FROM
                    orders
                WHERE
                    customer_id = c.customer_id
                    AND EXTRACT(
                        YEAR
                    FROM
                        order_date
                    
            )
        ORDER BY
            company;
Code language: SQL (Structured Query Language) (sql)

The following statement retrieves data from the customers_2016 table to verify the insert:

Oracle EXISTS vs. IN

The EXISTS operator stops scanning rows once the subquery returns the first row because it can determine the result whereas the IN operator must scan all rows returned by the subquery to conclude the result.

In addition, the IN clause can’t compare anything with NULL values, but the EXISTS clause can compare everything with NULL values. For example, the first statement returns no row while the second one returns all rows from the customers table:

SELECT
    *
FROM
    customers
WHERE
    customer_id IN(NULL);


SELECT
    *
FROM
    customers
WHERE
    EXISTS (
        SELECT
            NULL
        FROM
            dual
    );
Code language: SQL (Structured Query Language) (sql)

Typically, the EXISTS operator is faster than IN operator when the result set of the subquery is large. By contrast, the IN operator is faster than EXISTS operator when the result set of the subquery is small.

In this tutorial, you have learned how to use the Oracle EXISTS operator for testing the existence of the rows in a query.