Identify Items that are not Members of both Sets

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Author: Unknown

Date: 19 Jan 2012

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

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

)