Functional difference between “NOT IN” vs “NOT EXISTS” clauses
Posted by decipherinfosys on January 21, 2007
“NOT IN” and “NOT EXISTS” clauses are not the same functionally or performance wise and, therefore, should be used appropriately. This blog post outlines how these commands are executed and discusses when it is appropriate to use them.
Sample data:
/*******************************************************************************************
Create a dummy EMP_MASTER table populate it with some records for illustration. This is Oracle Syntax. There are ten employees that have been created and 9 out of those 10 report to their manager: Dennis who is at the head of the chain and does not have a manager to report to.
********************************************************************************************/
CREATE TABLE EMP_MASTER
(
EMP_NBR NUMBER(10) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR2(20 CHAR),
MGR_NBR NUMBER(10) NULL
)
/
INSERT INTO EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO EMP_MASTER VALUES (2, ‘HARI’, 5);
INSERT INTO EMP_MASTER VALUES (3, ‘RAMESH’, 5);
INSERT INTO EMP_MASTER VALUES (4, ‘JOE’, 5);
INSERT INTO EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO EMP_MASTER VALUES (7, ‘JESSIE’, 5);
INSERT INTO EMP_MASTER VALUES (8, ‘KEN’, 5);
INSERT INTO EMP_MASTER VALUES (9, ‘AMBER’, 5);
INSERT INTO EMP_MASTER VALUES (10, ‘JIM’, 5);
COMMIT
/
Resulting in:
EMP_NBR |
EMP_NAME | MGR_NBR |
1 | DON | 5 |
2 | HARI | 5 |
3 | RAMESH | 5 |
4 | JOE | 5 |
5 | DENNIS | NULL |
6 | NIMISH | 5 |
7 | JESSIE | 5 |
8 | KEN | 5 |
9 | AMBER | 5 |
10 | JIM | 5 |
Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.
NOT IN
SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );
COUNT(*)
———-
0
This means that everyone is a manager…hmmm, I wonder whether anything ever gets done in that case 🙂
NOT EXISTS
SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );
COUNT(*)
———-
9
Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back. (in MS SQL Server, depending upon the ANSI NULLS setting, the behavior can be altered but this post only talks about the behavior that is same in Oracle, DB2 LUW and MS SQL Server).
Performance implications:
When using “NOT IN”, the query performs nested full table scans, whereas for “NOT EXISTS”, query can use an index within the sub-query.
Another Optional Method
Another way of doing this is to use an outer join and check for NULL values in the other table:
SELECT COUNT(*)
FROM EMP_MASTER T1
LEFT OUTER JOIN EMP_MASTER T2
ON T1.EMP_NBR = T2.MGR_NBR
WHERE T2.MGR_NBR IS NULL
/
Of course, there should be other selection criteria as well (possibly a range search criteria, an equality SARG (searchable argument) criteria etc.) to help improve the selectivity besides just the NOT EXISTS clause.
5 Responses to “Functional difference between “NOT IN” vs “NOT EXISTS” clauses”
Sorry, the comment form is closed at this time.
IN vs EXISTS « Systems Engineering and RDBMS said
[…] in one scenario – read this post for the differences between NOT IN and NOT EXISTS clauses : https://decipherinfosys.wordpress.com/2007/01/21/32/ ). However, there are performance implications of using one over the other that one needs to be […]
workarounds for mysql NOT IN and NOT EXISTS information « Coral's Blog said
[…] It also seems that NOT IN is inclined to do full table scans on the subquery where as NOT EXISTS can use an index which means that but NOT EXISTS has better performance.. More information on the difference here. […]
IN vs. EXISTS vs. JOIN : Sean Holtshousen – Developer Blog said
[…] in one scenario – read this post for the differences between NOT IN and NOT EXISTS clauses : https://decipherinfosys.wordpress.com/2007/01/21/32/ ). However, there are performance implications of using one over the other that one needs to be […]
2010 in review – courtesy Wordpress.com « Systems Engineering and RDBMS said
[…] Functional difference between “NOT IN” vs “NOT EXISTS” clauses January 2007 3 comments 5 […]
Can you use multiple columns for a not in query? said
[…] https://decipherinfosys.wordpress.com/2007/01/21/32/ […]