SQL - 使用 EXCEPT 與 INTERSECT 來比對兩個資料表中的資料

摘要:T-SQL - 使用 EXCEPT 與 INTERSECT 來比對兩個資料表中的資料

在一些情況下我們會需要比對兩個資料表中的資料差異,怎麼說!? 我們就來用情境來說明吧。有一次,在測試機測試資料時,PM:「張小呆,今天在讀取資料時,發現在測試機會有對應不到資料的情況,但在正式機是正常的,請檢察正式機與測試機同一個資料表,有哪些資料是相同的,有哪些資料是正式機有而測試機是沒有的」。小呆:「是喔,看起來可以用比較傳統的做法,那就是 JOIN 或者 子查詢的方式」,PM:「這兩種方是我也會寫,但我希望有更方便,且快速的做法,所以再想想唄」。還好在 MS SQL 2005 時,貼心的微軟就提供了 EXCEPT 與 INTERSECT 來快速解決小呆的困惱,以下就用一個簡單的範例來說明...

Code:

--建立一個名為「名稱清單」的資料表
CREATE TABLE [DBO].[名稱清單](
 [UID] [VARCHAR](4) NOT NULL,
 [USERNAME] [NVARCHAR](10) NOT NULL,
 CONSTRAINT [PK_名稱清單] PRIMARY KEY CLUSTERED
(
 [UID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--對「名稱清單」資料表建立資料
DECLARE @ICOUNT INT
DECLARE @F_COUNTTOCHAR NVARCHAR(1)

SET @ICOUNT = 0

WHILE(@ICOUNT <= 5)
  BEGIN
 SET @F_COUNTTOCHAR = CONVERT(NVARCHAR(1), @ICOUNT)
 INSERT INTO DBO.名稱清單(UID, USERNAME)
 VALUES('100' + @F_COUNTTOCHAR, 'DANNY' + @F_COUNTTOCHAR)
 SET @ICOUNT = @ICOUNT + 1
  END

--查詢「名稱清單」的資料
SELECT * FROM DBO.名稱清單

--快速建立一個名為「名稱清單_BAK」的資料表,並將「名稱清單」DANNY1 與 DANNY2
--以外的資料建立到「名稱清單_BAK」
SELECT * INTO DBO.名稱清單_BAK FROM DBO.名稱清單
WHERE USERNAME NOT LIKE '%[1-2]'

--查詢「名稱清單_BAK」的資料
SELECT * FROM DBO.名稱清單_BAK

--使用 EXCEPT 來比對兩個資料表的資料
SELECT * FROM DBO.名稱清單
EXCEPT
SELECT * FROM DBO.名稱清單_BAK

--使用 INTERSECT 來比對兩個資料表的資料
SELECT * FROM DBO.名稱清單
INTERSECT
SELECT * FROM DBO.名稱清單_BAK


說明:[出處:MSDN]

比較兩個查詢的結果來傳回個別值。

EXCEPT 會從左側查詢中傳回在右側查詢中找不到的任何個別值。

INTERSECT 會傳回 INTERSECT 運算元左右兩側查詢都傳回的任何個別值。

使用 EXCEPT 或 INTERSECT 的兩個查詢,其結果集的基本組合規則如下:

在所有查詢中,資料行的數目和順序都必須相同。

資料類型必須相容。

參考:
EXCEPT 和 INTERSECT (Transact-SQL)