摘要: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)