You don't need 30 join conditions for a FULL OUTER JOIN here.
You can just Full Outer Join on the PK, preserve rows with at least one difference with WHERE EXISTS (SELECT A.* EXCEPT SELECT B.*) and use CROSS APPLY (SELECT A.* UNION ALL SELECT B.*) to unpivot out both sides of the JOINed rows into individual rows.
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
Gives
what Col1 Col2 Col3
------ ------ ----------- -----------
TableA Cat 27 86
TableB Cat 27 105
TableA Cat 128 92
TableB Lizard 83 NULL
Or a version dealing with the moved goalposts.
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
For tables with many columns it can still be difficult to identify the specific column(s) that differ. For that you can potentially use the below.
(though just on relatively small tables as otherwise this method likely won't have adequate performance)
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)
You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.
I'd suggest using a HAVING clause with the conditions. The query would be similar to:
select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
and sum(case when col2 is null then 1 else 0 end) = 1
See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.
You can then use this in a subquery, etc to get the username and col2 values:
select
t.username,
t.col2
from yourtable t
inner join
(
select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
and sum(case when col2 is null then 1 else 0 end) = 1
) d
on t.username = d.username
See SQL Fiddle with Demo.
If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:
select
t.username,
t.col2
from yourtable t
inner join
(
select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) >= 1
and sum(case when col2 is null then 1 else 0 end) >= 1
) d
on t.username = d.username;
See SQL Fiddle with Demo
Best Answer
Another way using
lag()to access the previous value andTOPto only get the latest.