Oracle NOT EXISTS

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.

Consider the following statement that uses the NOT EXISTS operator:

SELECT * FROM table_name WHERE 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 a NULL value.

Oracle NOT EXISTS examples

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

Customers and Orders tables

The following statement finds all customers who have no order:

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)

Oracle NOT EXISTS example
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)

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 );
Code language: SQL (Structured Query Language) (sql)

And to delete all customers who had no order 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 FROMorder_date ) IN( 2016, 2017 ) );
Code language: SQL (Structured Query Language) (sql)

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 = NULL
Code 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.

In this tutorial, you have learned how to use the Oracle NOT EXISTS operator to subtract one set of data from another.

Was this tutorial helpful?