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

In the previous article I have explained how to fill (bind) the Asp.net dropdownlistusing Jquery and JSON and How to implement carousel image slider in asp netusing flexisel responsive jquery.

I have created two tables Table1 and Table2. Insert the dummy records into both tables.

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