Sql-Server
輕鬆顯示兩個表或查詢之間不同的行
想像一下，您有兩個不同的表/查詢，它們應該具有/返回相同的數據。你想驗證這一點。什麼是顯示每個表中任何不匹配行的簡單方法，就像下面的範例一樣，比較每一列？假設表中有 30 列，其中許多列可以為 NULL。
當沒有 PK 或每個 PK 可能有重複項時，僅加入 PK 列是不夠的，並且必須使用 30 個正確處理 NULL 的連接條件以及令人討厭的 WHERE 條件進行 FULL JOIN 將是一場災難排除匹配的行。
通常，當我針對未清理或未完全理解的數據編寫新查詢時，問題最嚴重，並且 PK 在邏輯上可用的可能性極低。我設計了兩種不同的方法來解決問題，然後比較它們的結果，這些差異突出了我不知道的數據中的特殊情況。
結果需要如下所示：
Which Col1 Col2 Col3 ... Col30 ------ ------ ------ ------ ------ TableA Cat 27 86 -- mismatch TableB Cat 27 105 -- mismatch TableB Cat 27 87 -- mismatch 2 TableA Cat 128 92 -- no corresponding row TableB Lizard 83 NULL -- no corresponding row如果
[Col1, Col2]確實是一個複合鍵，並且我們在最終結果中按它們排序，那麼我們可以很容易地看到 A 和 B 有一行不同但應該是相同的，並且每一行都有另一行不存在。在上面的範例中，不希望看到第一行兩次。
這是用於設置範例表和數據的 DDL 和 DML：
CREATE TABLE dbo.TableA ( Col1 varchar(10), Col2 int, Col3 int, Col4 varchar(10), Col5 varchar(10), Col6 varchar(10), Col7 varchar(10), Col8 varchar(10), Col9 varchar(10), Col10 varchar(10), Col11 varchar(10), Col12 varchar(10), Col13 varchar(10), Col14 varchar(10), Col15 varchar(10), Col16 varchar(10), Col17 varchar(10), Col18 varchar(10), Col19 varchar(10), Col20 varchar(10), Col21 varchar(10), Col22 varchar(10), Col23 varchar(10), Col24 varchar(10), Col25 varchar(10), Col26 varchar(10), Col27 varchar(10), Col28 varchar(10), Col29 varchar(10), Col30 varchar(10) ); CREATE TABLE dbo.TableB ( Col1 varchar(10), Col2 int, Col3 int, Col4 varchar(10), Col5 varchar(10), Col6 varchar(10), Col7 varchar(10), Col8 varchar(10), Col9 varchar(10), Col10 varchar(10), Col11 varchar(10), Col12 varchar(10), Col13 varchar(10), Col14 varchar(10), Col15 varchar(10), Col16 varchar(10), Col17 varchar(10), Col18 varchar(10), Col19 varchar(10), Col20 varchar(10), Col21 varchar(10), Col22 varchar(10), Col23 varchar(10), Col24 varchar(10), Col25 varchar(10), Col26 varchar(10), Col27 varchar(10), Col28 varchar(10), Col29 varchar(10), Col30 varchar(10) ); INSERT dbo.TableA (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29, Col30) VALUES ('Cat', 27, 86, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0'), ('Cat', 128, 92, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0'), ('Porcupine', NULL, 42, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0'), ('Tapir', NULL, NULL, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0') ; INSERT dbo.TableB (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29, Col30) VALUES ('Cat', 27, 105, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0'), ('Cat', 27, 87, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0'), ('Lizard', 83, NULL, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0'), ('Porcupine', NULL, 42, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0'), ('Tapir', NULL, NULL, 'a', 'b', 'c', 'd', 'e', 'f', 'g',' h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0');
這裡不需要 30 個加入條件
FULL OUTER JOIN。您可以只在 PK 上進行完全外連接，保留至少有一個差異的行，
WHERE EXISTS (SELECT A.* EXCEPT SELECT B.*)並用於CROSS APPLY (SELECT A.* UNION ALL SELECT B.*)將 ed 行的兩側反旋轉JOIN成單獨的行。WITH TableA(Col1, Col2, Col3) AS (SELECT 'Dog',1,1 UNION ALL SELECT 'Cat',27,86 UNION ALL SELECT 'Cat',128,92), TableB(Col1, Col2, Col3) AS (SELECT 'Dog',1,1 UNION ALL SELECT 'Cat',27,105 UNION ALL SELECT 'Lizard',83,NULL) SELECT CA.* FROM TableA A FULL OUTER JOIN TableB B ON A.Col1 = B.Col1 AND A.Col2 = B.Col2 /*Unpivot the joined rows*/ CROSS APPLY (SELECT 'TableA' AS what, A.* UNION ALL SELECT 'TableB' AS what, B.*) AS CA /*Exclude identical rows*/ WHERE EXISTS (SELECT A.* EXCEPT SELECT B.*) /*Discard NULL extended row*/ AND CA.Col1 IS NOT NULL ORDER BY CA.Col1, CA.Col2給
what Col1 Col2 Col3 ------ ------ ----------- ----------- TableA Cat 27 86 TableB Cat 27 105 TableA Cat 128 92 TableB Lizard 83 NULL或處理移動球門柱的版本。
SELECT DISTINCT CA.* FROM TableA A FULL OUTER JOIN TableB B ON EXISTS (SELECT A.* INTERSECT SELECT B.*) CROSS APPLY (SELECT 'TableA' AS what, A.* UNION ALL SELECT 'TableB' AS what, B.*) AS CA WHERE NOT EXISTS (SELECT A.* INTERSECT SELECT B.*) AND CA.Col1 IS NOT NULL ORDER BY CA.Col1, CA.Col2對於具有許多列的表，仍然很難辨識不同的特定列。為此，您可以使用以下內容。
（雖然只是在相對較小的表上，否則這種方法可能不會有足夠的性能）
SELECT t1.primary_key, y1.c, y1.v, y2.v FROM t1 JOIN t2 ON t1.primary_key = t2.primary_key CROSS APPLY (SELECT t1.* FOR xml path('row'), elements xsinil, type) x1(x) CROSS APPLY (SELECT t2.* FOR xml path('row'), elements xsinil, type) x2(x) CROSS APPLY (SELECT n.n.value('local-name(.)', 'sysname'), n.n.value('.', 'nvarchar(max)') FROM x1.x.nodes('row/*') AS n(n)) y1(c, v) CROSS APPLY (SELECT n.n.value('local-name(.)', 'sysname'), n.n.value('.', 'nvarchar(max)') FROM x2.x.nodes('row/*') AS n(n)) y2(c, v) WHERE y1.c = y2.c AND EXISTS(SELECT y1.v EXCEPT SELECT y2.v)