Anti-Join methods: NOT EXISTS vs NOT IN

Published:
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
3,449 Views

Comments (0)

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.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community