SQL Server MINUS and INTERSECT

SQL Server 2005 後,提供EXCEP 和INTERSECT, 功能和ORACLE的MINUS, INTERSECT相同

EXCEP 找出存在於第一個子集,但不存在於第二個子集的筆數
INTERSECT 找出皆存在於二個子集中的筆數

這二個方法很方便查找有多個欄位的二個子集差異


以下是SQL 2008 ONLINE HELP說明
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

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

USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;
--Result: 238 Rows (products that have work orders)


The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query.

USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;
--Result: 266 Rows (products without work orders)

以前沒有這種比較的需求, 也沒想過會有EXCEPT,INTERSECT這種語法想了一下,使用left outer join , right outer join  ,inner join 或exists 可以做出同樣的結果嗎???

簡單的測試....., select 的欄位中,含有NULL 值, 則預期結果大不相同

--測試資料
select * into #test1 from
(select  'A11' as col1 , cast(null  as varchar(10))  col2 ,'A31'  col3
union all
select 'A21', null ,'A33'
) a
select * into #test2 from
(select  'A11' as col1 , 'A12'  col2 ,'A31'  col3
union all
select 'A21', null ,'A33'
) a

--EXCEPT
select * from #test1
except
select * from #test2

--回傳結果找出一筆不同
--col1    col2    col3
--A11    NULL    A31

--LEFT OUTER JOIN
select *
from #test1 a left outer join #test2  b
   on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3

--回傳結果找出二筆 ,若過濾 掉 b.col1 is null, 則無符合筆數
--A11    NULL    A31    NULL    NULL    NULL
--A21    NULL    A33    NULL    NULL    NULL