<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Anti-Join methods: NOT EXISTS vs NOT IN

Published on
9,471 Points
3,171 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
0 Comments

Featured Post

Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month