T-SQL 運算式 - EXCEPT 與 INTERSECT

當兩個資料集多個欄位交叉比對時,可以採用 EXCEPT 或 INTERSECT 運算式,其結果會類似:

  • EXCEPT 等於 NOT IN + DISTINCT 的語法
  • INTERSECT 與 IN + DISTINCT 的語法


基本語法範例
CREATE TABLE Product (
 ProductID int
);
GO
CREATE TABLE WorkOrder(
 ProductID int 
);
GO
TRUNCATE TABLE Product
TRUNCATE TABLE WorkOrder
GO

INSERT INTO Product(ProductID)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ;
GO


INSERT INTO WorkOrder(ProductID)
SELECT 2 UNION ALL
SELECT 3 ;
GO

/*IN 的寫法*/
SELECT N'IN 的寫法', ProductID   
FROM Product  
WHERE ProductID IN
(
       SELECT ProductID   
       FROM WorkOrder
)
GO
/*INTERSECT 的寫法*/
SELECT N'INTERSECT 的寫法', *
FROM(
 SELECT ProductID   
 FROM Product  
 INTERSECT  
 SELECT ProductID   
 FROM WorkOrder
)T
GO
/*NOT IN 的寫法*/
SELECT N'NOT IN 的寫法', ProductID   
FROM Product  
WHERE ProductID NOT IN
(
       SELECT ProductID   
       FROM WorkOrder
)
GO
/*EXCEPT 的寫法*/
SELECT N'EXCEPT 的寫法', *
FROM(
SELECT ProductID   
FROM Product  
EXCEPT  
SELECT ProductID   
FROM WorkOrder
)T
GO

DROP TABLE Product;
DROP TABLE WorkOrder;
GO

多個欄位的篩選比對
CREATE TABLE Product (
       ProductID int
    , PCode varchar(1)
);
GO
CREATE TABLE WorkOrder(
       ProductID int
    , PCode varchar(1)
);
GO
INSERT INTO Product(ProductID, PCode)
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'A' UNION ALL
SELECT 4, 'A' UNION ALL
SELECT 5, 'A' ;
GO

INSERT INTO WorkOrder(ProductID, PCode)
SELECT 2, 'A' UNION ALL
SELECT 3, 'A' ;
GO

SELECT ProductID, PCode 
FROM Product  
EXCEPT  
SELECT ProductID, PCode
FROM WorkOrder
GO

DROP TABLE Product;
DROP TABLE WorkOrder;
GO

執行效能比較
(1) NOT IN 
(2) NOT EXISTS 
(3) SQL LEFT JOIN
(4) SQL EXCEPT

若篩選欄位沒有建立索引 
(2) NOT EXISTS  與 (3) SQL LEFT JOIN 速度最快
image

若篩選欄位有建立索引 
(2) NOT EXISTS  與 (4) SQL EXCEPT 速度最快
image

效能測試範例
CREATE TABLE Product (
 ProductID int
);
GO
CREATE TABLE WorkOrder(
 ProductID int 
);
GO
CREATE NONCLUSTERED INDEX IX_Product ON Product
(
  ProductID ASC
)
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder ON WorkOrder
(
  ProductID ASC
)
GO

TRUNCATE TABLE Product
TRUNCATE TABLE WorkOrder
GO

DECLARE @count int =1, @max int =10000

WHILE @count<=@max
BEGIN
 INSERT INTO Product(ProductID)
 SELECT FLOOR(RAND()*(20000));

 INSERT INTO WorkOrder(ProductID)
 SELECT FLOOR(RAND()*(20000));

 SET @count = @count + 1 
END
GO

SELECT COUNT(1) from Product
SELECT COUNT(1) from WorkOrder
GO

/*NOT IN 的寫法*/
SELECT ProductID   
FROM Product  
WHERE ProductID NOT IN
(
 SELECT ProductID   
 FROM WorkOrder
)
GO

/*NOT EXISTS*/
SELECT A.ProductID
FROM Product A 
WHERE NOT EXISTS (
 SELECT B.ProductID FROM WorkOrder B WHERE B.ProductID = A.ProductID
)
GO

/*LEFT JOIN*/
SELECT A.ProductID 
FROM Product A
LEFT OUTER JOIN WorkOrder B
 on A.ProductID=B.ProductID
WHERE B.ProductID IS NULL
GO

/*EXCEPT 的寫法*/
SELECT ProductID   
FROM Product  
EXCEPT  
SELECT ProductID   
FROM WorkOrder
GO

DROP TABLE Product;
DROP TABLE WorkOrder;
GO

相關參考
微軟 Set Operators - EXCEPT and INTERSECT (Transact-SQL)
T-SQL commands performance comparison – NOT IN vs SQL NOT EXISTS vs SQL LEFT JOIN vs SQL EXCEPT
Performance Comparison EXCEPT vs NOT IN

這個網誌中的熱門文章

Google Map 多點路線規劃

解決瀏覽器無法下載檔案