Oracle NOT EXISTS Operator

Summary: in this tutorial, you learn how to use the Oracle NOT EXISTS operator to subtract one set of data from another.

Introduction to the Oracle NOT EXISTS operator #

The NOT EXISTS operator works the opposite of the EXISTS operator. We often use the NOT EXISTS operator with a subquery to subtract one set of data from another.

Here’s the syntax of the NOT EXISTS operator:

NOT EXISTS (subquery);Code language: SQL (Structured Query Language) (sql)

The NOT EXISTS operator returns true if the subquery returns no row. Otherwise, it returns false.

Note that the NOT EXISTS operator returns false if the subquery returns any rows with NULL.

Oracle NOT EXISTS examples #

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

The following statement uses the NOT EXISTS operator to find customers who have no order:

Try it

SELECT
  name
FROM
  customers
WHERE
  NOT EXISTS (
    SELECT
      NULL
    FROM
      orders
    WHERE
      orders.customer_id = customers.customer_id
  )
ORDER BY
  name;Code language: SQL (Structured Query Language) (sql)

To archive the customers who have no order, you use the following statement:

CREATE TABLE customers_archive AS
SELECT
  *
FROM
  customers
WHERE
  NOT EXISTS (
    SELECT
      NULL
    FROM
      orders
    WHERE
      orders.customer_id = customers.customer_id
  );Code language: SQL (Structured Query Language) (sql)

Try it

To update the credit limit of customers who have no order in 2017, you use the following UPDATE statement:

UPDATE customers
SET
  credit_limit = 0
WHERE
  NOT EXISTS (
    SELECT
      NULL
    FROM
      orders
    WHERE
      orders.customer_id = customers.customer_id
      AND EXTRACT(
        YEAR
        FROM
          order_date
      ) = 2017
  );Code language: SQL (Structured Query Language) (sql)

Try it

And to delete all customers who had no orders in 2016 and 2017 from the customers table, you use the following DELETE statement:

DELETE FROM customers
WHERE
  NOT EXISTS (
    SELECT
      NULL
    FROM
      orders
    WHERE
      orders.customer_id = customers.customer_id
      AND EXTRACT(
        YEAR
        FROM
          order_date
      ) IN (2016, 2017)
  );Code language: SQL (Structured Query Language) (sql)

Try it

Oracle NOT EXISTS vs. NOT IN #

The following statement uses the IN operator with a subquery:

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

Suppose the subquery returns four values 1, 2, 3, and NULL. You can rewrite the whole query above as follows:

SELECT
    *
FROM
    table_name
WHERE
    id = 1
    OR id = 2  
    OR id = 3
    OR id = NULL;
Code language: SQL (Structured Query Language) (sql)

The following expression always returns a NULL value because a NULL value cannot compare to anything.

id = NULLCode language: SQL (Structured Query Language) (sql)

Therefore, the following expression returns a NULL value if any row in the result set of the subquery is NULL.

id NOT IN (subquery)Code language: SQL (Structured Query Language) (sql)

In contrast, NULL does not affect the result of the NOT EXIST operator because the NOT EXISTS operator solely checks the existence of rows in the subquery:

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

In conclusion, the NOT EXISTS and NOT IN behave differently when there are null values involved.

Summary #

• Use the Oracle NOT EXISTS operator to subtract one set of data from another.