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.
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;
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).NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER even -- if an "anti-join" can be used (if the sub-query is known to not return nulls)
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
Steve on Apr 16, 2020 at 12:10 am
This article is contributed by Steve. If you like dEexams.com and would like to contribute, you can write your article here or mail your article to email@example.com . See your article appearing on the dEexams.com main page and help others to learn.