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
