Browse All Articles > Anti-Join methods: NOT EXISTS vs NOT IN
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.
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.
By using NOT EXISTS and index scan on stock(item) can be used which is faster than a full table scan.
A common approach would be to code it like this.
SELECT * FROM GOODS
WHERE ITEM NOT IN (SELECT ITEM FROM STOCK);
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 |
----------------------------------------------------------------------------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);
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 |
------------------------------------------------------------------------------By using NOT EXISTS and index scan on stock(item) can be used which is faster than a full table scan.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)