<

Anti-Join methods: NOT EXISTS vs NOT IN

Published on
9,409 Points
3,109 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
Comment
Author:tomcatkev
0 Comments

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Join & Write a Comment

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month