INTERSECT和WHERE的区别是什么?

INTERSECT EXCEPT 比较返回的行中所有选定的值 WHERE IN WHERE NOT IN 一次只比较一列。

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

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);

.

列
-----------

(0行受影响)