Keyword TSQL INTERSECT

Question

I have known the SQL Server 2005/2008 EXCEPT and INTERSECT keywords for some time, but have never found a good reason to use it INTERSECTover the old old UNION.

Can someone explain when or why you would use INTERSECTinstead UNION?

Answer 1

They do different things.

From MSDN:

EXCEPT returns any single values ​​from the left query that are also not found in the correct query.

INTERSECT , INTERSECT.

UNION , , .

Answer 2

INTERSECT ,

UNION , .

EXCEPT , , .

INTERSECT EXCEPT , , IN/NOT IN.

, SQL Server IN .

INTERSECT EXCEPT, JOIN IN, NULL . :

SELECT  NULL
INTERSECT
SELECT  NULL

, :

SELECT  NULL
WHERE   NULL IN
        (
        SELECT  NULL
        )

.

Answer 3

INTERSECT INNER JOINS, , .

- . , : "", , "", . , EXCEPT. (: LEFT JOIN.. WHERE left_site IS NULL)