Hi Friends,
Last week, I was in an assignment and one of the guys asked this question: “How to Return non matching records from two tables?”
So, here goes the scenario. There are two tables, say, Table1 & Table2 and both of them have a column, say col1. And you want to return records that are unique to each table (non-matching) based on col1.
Table1
Col1
AAA
BBB
CCC
Table2
Col1
AAA
BBB
ZZZ
So, you need to write a query that returns CCC & ZZZ.
Here is the solution:
( select col1 from table1 t1 EXCEPT Select col1 from table2 t2 ) UNION ( select col1 from table2 t2 EXCEPT Select col1 from table1 t1 )
There are other techniques too to solve the above problem. Do post comments and let the readers know about your solution.
You can try this too(i have used temporary tables just for my comfort) :
SELECT a.*, b.*
FROM #ABC a
full join #XYZ b ON a.aid = b.bid
WHERE b.bid IS NULL or a.aid IS NULL
You should look at the generic SP CompareRows in this month SQL Server Magazine article. This one show also differences on both the source and the target and properly identify them, and scan source only once.
http://www.sqlmag.com/article/sql-server/improve-sql-dynamic-coding-practices-141080
This procedure is able to compare Tables/Query results across tables in the same databases or different databases or differents servers (provided that linked server are defined to access remote server).
It also demonstrate great ways to generate dynamic sql code.
You can get the code at : http://www.sqlmag.com/content/content/141080/141080.zip
use madworks
go
create table #t1
(
col1 char(3)
)
create table #t2
(
col2 char(3)
)
insert into #t1
select ‘AAA’
UNION ALL
select ‘BBB’
UNION ALL
select ‘CCC’
Insert into #t2
select ‘AAA’
UNION ALL
select ‘BBB’
UNION ALL
select ‘ZZZ’
;
with cte as (
select Col1 as Col from #t1
Union all
select Col2 from #t2
)
select col from cte
group by col Having Count(*) =1
Drop table #t1
Drop table #t2
I definitely prefer the solution using the FULL JOIN, as it’s portable (lucky you if you can really focus only on SQL Server, I never had such luck in 14 years of career). Besides, unless requirements are more complex than just returning the unique rows, its performance is not so bad.
Thanks much to the readers for posting valuable comments, it does help !
The solution with the CTE is compatible with all DB that support ANSI99. This includes Oracle, SQL Server, DB2 and probably Informix and PostGres even in no so recent versions. Don’t know for the others.
Full join solution may be cumbersome to code when you have two records with the same key (both record exists) and you want to find a difference on a set of columns to understand on what they differ.
By the way you can may this compatible to old versions by replacing
“from cte”
by
from (select Col1 as Col from #t1
Union all
select Col2 from #t2
) as Cte
This is called a derived table and this work on most of old versions of rdbms and many low end ones.
–Could you not also do this?(
select col1 from table1 t1
UNION
Select col1 from table2 t2
)
EXCEPT
(
select col1 from table2 t2
INTERSECT
Select col1 from table1 t1
)