<

Anti-Join methods: NOT EXISTS vs NOT IN

Published on
9,564 Points
3,264 Views
3 Endorsements
Last Modified:
Approved
Often a need arises to choose rows from one table that does not have a matching row in another table, such as searching for goods that are not currently in stock.

A common approach would be to code it like this.

SELECT * FROM GOODS
WHERE ITEM NOT IN (SELECT ITEM FROM STOCK);

6349 rows selected.

Elapsed: 00:00:16.14

Execution Plan
----------------------------------------------------------
Plan hash value: 3039763702

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 12694 |   123K| 55315   (4)| 00:11:04 |
|*  1 |  FILTER            |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GOODS | 12695 |   123K|  8927   (1)| 00:01:48 |
|*  3 |   TABLE ACCESS FULL| STOCK |     1 |     5 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

Open in new window



If we check the execution plan we may see full table scans on both tables without any performance benefits of index scans or seeks.

An alternative approach to code the SQL using NOT EXISTS.

SELECT * FROM GOODS A
WHERE NOT EXISTS (SELECT 1 FROM STOCK B WHERE A.ITEM=B.ITEM);

6349 rows selected.

Elapsed: 00:00:00.81

Execution Plan
----------------------------------------------------------
Plan hash value: 4120760422

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |  6349 | 95235 |  8933   (1)| 00:01:48 |
|   1 |  NESTED LOOPS ANTI |         |  6349 | 95235 |  8933   (1)| 00:01:48 |
|   2 |   TABLE ACCESS FULL| GOODS   | 12695 |   123K|  8927   (1)| 00:01:48 |
|*  3 |   INDEX UNIQUE SCAN| PKSTOCK |  3172 | 15860 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Open in new window



By using NOT EXISTS and index scan on stock(item) can be used which is faster than a full table scan.
3
Author:tomcatkev
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free