Cool Tips‎ > ‎Data Deduplication‎ > ‎

Data Deduplication - Identify Items that are not Members of both Sets

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        19 Jan 2012

Description

Two ANSI SQL commands were introduced in SQL 2005.  These are INTERSECT and EXCEPT.
 
SET_A INTERSECT SET_B returns the common items from two identically structured sets #A and #B
SET_A EXCEPT SET_B returns set #A excluding items that appear in Set #B
EXCEPT has execution precendence over INTERSECT
 
These two commands can be used together to quickly and easily produce a list of items that do not appear in both sets #A and #B
 

Code

 

DECLARE @Set_A AS TABLE (foo INT)
DECLARE @Set_B AS TABLE (foo INT)

INSERT INTO @Set_A
SELECT 1
UNION
SELECT
2
UNION
SELECT
5

INSERT INTO @Set_B
SELECT 1
UNION
SELECT
2
UNION
SELECT
4


-- intersect has a higher precedence than except, but it is clearer if queries are written using brackets
-- #1 get all items from both sets
SELECT * FROM @Set_A
UNION
SELECT
* FROM @Set_B

-- #3 remove common items from the set of all items to give the items that are not common across both
EXCEPT
(
  
-- #2 get the common items between the sets
  
SELECT * FROM @Set_A
  
INTERSECT
   SELECT
* FROM @Set_B
)  


ċ
Get data that does not appear in both sets.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments