Oracle EXISTS Operator

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)

Try it

Output:

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)

Try it

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)

Try it

Output:

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)

Try it

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
      ) = 2016
  )
ORDER BY
  company;Code language: SQL (Structured Query Language) (sql)

Try it

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

SELECT
  *
FROM
  customers_2016;

Output:

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:

SELECT
  *
FROM
  customers
WHERE
  customer_id IN (NULL);Code language: PHP (php)

Whereas the following statment returns all rows from the customers table:

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.