CODEWITHSUNDEEP
sqlsql-servert-sqlouter-join
WV PM
WV PM

Reputation: 151

Sql compare tables / full outer join with filter on both sides

Real world case

I have a table (in MS SQL Server) which has the following columns: AccountId, RunId and Details

I want to compare 2 'runs' and find the differences between runs.

To simplify the case, I made the following sample.

Simplified case

CREATE TABLE #T (
    AccountId INT NOT NULL,
    RunId INT NOT NULL
)

INSERT INTO #T
(AccountId , RunId)
VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 1)
DROP TABLE #T

Desired output (; seperated)

AccountId;Run1;Run2
2;NULL;2
3;1;NULL

Working solutions so far

1:

SELECT COALESCE(T1.AccountId, T2.AccountId) [AccountId], T1.RunId [Run1], T2.RunId [Run2]
FROM #T T1
FULL OUTER JOIN #T T2 ON T2.AccountId = T1.AccountId AND T2.RunId = 2 AND T1.RunId = 1
WHERE (T1.RunId = 1 OR T1.RunId IS NULL)
AND (T2.RunId = 2 OR T2.RunId IS NULL)
AND (T1.AccountId IS NULL OR T2.AccountId IS NULL)
ORDER BY [AccountId]

2:

SELECT COALESCE(T1.AccountId, T2.AccountId) [AccountId], T1.RunId [Run1], T2.RunId [Run2]
FROM (SELECT TOP 1 0 [0]) AS [T0]
FULL OUTER JOIN #T T1 ON T1.RunId = 1
FULL OUTER JOIN #T T2 ON T2.AccountId = T1.AccountId AND T2.RunId = 2 AND [0] = 0
WHERE ([0] = 0 OR T2.RunId = 2)
AND (T1.AccountId IS NULL OR T2.AccountId IS NULL)
ORDER BY [AccountId]

3: 

SELECT COALESCE(T1.AccountId, T2.AccountId) [AccountId], T1.RunId [Run1], T2.RunId [Run2]
FROM (
    SELECT * FROM #T WHERE #T.RunId = 1
) T1
FULL OUTER JOIN (
    SELECT * FROM #T WHERE #T.RunId = 2
) T2 ON T2.AccountId = T1.AccountId
WHERE (T1.AccountId IS NULL OR T2.AccountId IS NULL)

My actual question

All three solutions are rather ugly. Isn't there an easier way?

If not, which of the three do you think is best?

Upvotes: 0

Views: 1114

Answers (2)

George Menoutis
George Menoutis

Reputation: 7240

My everyday solution for comparing two identical tables is:

-- t1 rows not in t2
select * from t1
except
select * from t2

-- t2 rows not in t1
select * from t2
except
select * from t1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

How about:

select accountid, min(runid)
from t
where runid in (1, 2)
group by accountid
having count(*) = 1;

This will show you the accounts only in one run, along with the run they are in.

Upvotes: 2

Related Questions