想象一下,您有两个不同的表/查询应该具有/返回相同的数据.你想验证这一点.如下例所示,比较每一列,从每个表中显示任何不匹配行的简单方法是什么？假设表中有30列,其中许多是NULLable.
当没有PK或每个PK可能存在重复时,仅加入PK列是不够的,如果必须使用正确处理NULL的30个连接条件进行全连接,加上令人讨厌的WHERE条件将是一场灾难.排除匹配的行.
通常情况下,当我正在编写一个新的查询时,针对未刷新或未完全理解的数据,问题最严重,并且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),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,('Porcupine',NULL,42,('Tapir','0')
;
INSERT dbo.TableB (Col1,105,87,('Lizard',83,'0');
解决方法
这里的FULL OUTER JOIN不需要30个连接条件.
您可以在PK上进行全外连接,保留与WHERE EXISTS(SELECT A. * EXCEPT SELECT B. *)至少有一个区别的行,并使用CROSS APPLY(SELECT A. * UNION ALL SELECT B. *)来取消JOINed行的两侧分成各行.
WITH TableA(Col1,Col3)
AS (SELECT 'Dog',1,1 UNION ALL
SELECT 'Cat',86 UNION ALL
SELECT 'Cat',92),TableB(Col1,105 UNION ALL
SELECT 'Lizard',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,B.*) AS CA
WHERE NOT EXISTS (SELECT A.* INTERSECT SELECT B.*)
AND CA.Col1 IS NOT NULL
ORDER BY CA.Col1,CA.Col2
