What is the difference between INTERSECT and WHERE IN?

Question

I wrote and went through 70-433 (MS SQL 2008 exam) and looked through when I realized that I was doing what INTERSECT does with the WHERE IN subquery, and EXCEPT with the WHERE NOT IN subquery.

Are there differences between using 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 compare only 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 NULL s

 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)