Wednesday, February 3, 2016

Find unmatched records in sql server

In this article I am going to explain how to find the unmatched records from two tables in sql server


I have created two tables Table1 and Table2. Insert the dummy records into both tables.
Find unmatched records in sql server

Query 1: using NOT IN
Select * from table1 where col1 not in (Select distinct col1 from table2 where col1 is not null)


Query 2: using NOT EXISTS
Select * from table1 t1 where not exists (Select * from table2 t2 where t1.col1 = t2.col1)

Query 3: using INTERSECT and EXCEPT
(select col1 from table1
union
select col1 from table2)
except
(select col1 from table2
intersect
select col1 from table1)

OR


(select col1 from table1
except
select col1 from table2)
union
(select col1 from table2
except
select col1 from table1)

Query 4: using CTE
with cte as
(
select col1 from table1
except
select col1 from table2
)
select * from cte

Query 5: using JOIN 

Select * from Table1 t1 left outer join table2 t2 on t1.col1 = t2.col1 where t2.col1 is null

No comments:

Post a Comment