TSQL INTERSECT Keyword

Question

I have known about the SQL Server 2005/2008 keywords EXCEPT and INTERSECT for some time but have never found a compelling reason to use INTERSECT over the good old UNION keyword.

Can anyone explain when or why you would use INTERSECT instead of UNION?

Answer 1

They do different things.

From MSDN:

EXCEPT returns any distinct values from the left query that are not also found on the right query.

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

UNION Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

Answer 2

INTERSECT returns records found in both recordsets

UNION returns records found in any of the recordsets.

EXCEPT returns records found in the first recordset but not in the second one.

Since both INTERSECT and EXCEPT can only return the records found in the first recordset, they can be rewritten using joins or IN / NOT IN predicates.

However, they are sometimes more convenient, since SQL Server does not support IN expressions on multiple columns.

INTERSECT and EXCEPT, unlike JOIN and IN, treat NULL values as matching. This query:

SELECT  NULL
INTERSECT
SELECT  NULL

returns a record, while this one:

SELECT  NULL
WHERE   NULL IN
        (
        SELECT  NULL
        )

returns nothing.

Answer 3

The INTERSECT usually can be replaces by INNER JOINS so may be it is the reason you don't find them useful.

However EXCEPT is quite interesting feature. For example think of a simple tagging system: You tag each user with a "badge" and you want to find all users that don't have a "critic" badge for example. You can do that easily using the EXCEPT feature. (Though: This can be also represented as LEFT JOIN .. WHERE left_site IS NULL)