How to find non-matching rows using EXCEPT in SQL

In this article, i am going to explain how to select non-matching rows using EXCEPT in SQL. I am creating two temporary table to demonstrate the same.In Short, EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table. One more interesting point to know, NOT IN also works like EXCEPT operator but it will not remove duplicate rows from the result.

to create and insert table data

create table #one (id int,name nvarchar(50))
insert into #one (id , name) values(1,'a') 
insert into #one (id , name) values(2,'b') 
insert into #one (id , name) values(3,'c') 

create table #two (id int,name nvarchar(50))
insert into #two (id , name) values(1,'a') 
insert into #two (id , name) values(3,'b') 
insert into #two (id , name) values(4,'c') 

to select by using query

select id from #one EXCEPT select id from #two 

Output

One basic rule of EXCEPT operator, you must have to specify number of columns and order same in all queries.

That’s it!!…..Happy Programming...