Oracle NOT EXISTS operator

Consider the below query

SELECT
    *
FROM
    table_name
WHERE
    NOT EXISTS (subquery);

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

Example

The following statement finds all customers who have no order in 2018 and 2019:

SELECT
    name
FROM
    customers
WHERE
    NOT EXISTS (
        SELECT
            NULL
        FROM
            orders
        WHERE
            orders.customer_id = customers.customer_id
            AND EXTRACT(
                YEAR FROM order_date
            ) IN(
                2018,
                2019
            )
    )
ORDER BY
    name;

NOT IN (vs) NOT EXISTS

It truly depends on the query and the data as to which is BEST.

Note that in general, NOT IN and NOT EXISTS are NOT the same!!!

SELECT COUNT(*) FROM emp WHERE empno NOT IN (SELECT mgr FROM emp); 

COUNT(*)
----------
0

Apparently there are NO rows such that an employee is not a mgr -- everyone is a mgr (or are they ?)

SELECT COUNT(*) FROM emp e1 WHERE NOT EXISTS (SELECT NULL FROM emp e2 WHERE e1.empno=e2.mgr);

COUNT(*)
----------
8

But now there are 8 people who are not managers. Beware the NULL value and NOT IN!! (also the reason why NOT IN is sometimes avoided).

The following statement uses the IN operator with a sub-query:

SELECT
 *
FROM
 table_name
WHERE
 id IN(subquery);
 

Suppose the sub-query 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;
  

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

Because NULL means -- I don't know. (literally, null means Unknown)

So, the predicate

where x not in ( NULL )

evaluates to neither TRUE, nor FALSE

SELECT * FROM dual WHERE dummy NOT IN ( NULL ); --no rows return

SELECT * FROM dual WHERE NOT( dummy NOT IN (NULL) ); --no rows return

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

id NOT IN (subquery)

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);

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

Also see IN vs EXISTS