What is the difference between INTERSECT and WHERE IN?

Question

I have written and passed 70-433 (SQL 2008 MCTS exam) and was reviewing when I realised that I have been doing what INTERSECT does with a WHERE IN subquery, as well as EXCEPT with a WHERE NOT IN subquery.

Are there any differences between using the new commands instead of subqueries?

Answer 1

INTERSECT and EXCEPT compare all the selected values in the rows returned while WHERE IN and WHERE NOT IN only compare one column at a time.

SELECT name,date from customers
EXCEPT
SELECT name, date from orders

Answer 2

There is an important difference in how they handle NULLs

WITH T1 AS(SELECT 1 AS COL UNION SELECT NULL),
     T2 AS (SELECT 2 AS COL UNION SELECT NULL)
SELECT * FROM T1 
INTERSECT 
SELECT * FROM T2;

.

COL
-----------
NULL

(1 row(s) affected)

.

WITH T1 AS(SELECT 1 AS COL UNION SELECT NULL),
     T2 AS (SELECT 2 AS COL UNION SELECT NULL)
SELECT * FROM T1 WHERE COL IN (SELECT COL FROM T2) ;

.

COL
-----------

(0 row(s) affected)

.

WITH T1 AS(SELECT 1 AS COL UNION SELECT NULL),
     T2 AS (SELECT 2 AS COL UNION SELECT NULL)
SELECT * FROM T1 
EXCEPT 
SELECT * FROM T2;

.

COL
-----------
1

(1 row(s) affected)

.

WITH T1 AS(SELECT 1 AS COL UNION SELECT NULL),
     T2 AS (SELECT 2 AS COL UNION SELECT NULL)
SELECT * FROM T1 WHERE COL NOT IN (SELECT COL FROM T2);

.

COL
-----------

(0 row(s) affected)