EXCEPT is available in SQL Server starting with SQL Server 2008. EXCEPT returns distinct rows by comparing the results of two queries. EXCEPT returns distinct rows from the left (first) input query that aren’t output by the right (last) input query. The data and tables are sourced from Philip Burton’s SQL course at Udemy.com. Contrast how EXCEPT works with UNION and INTERSECT.
Consider the following t-SQL code.
1 | select * from tblDepartment; |
7 | delete from tblDepartmentNew where Department = 'HR'; |
9 | select * from tblDepartment |
11 | select * from tblDepartmentNew |
Here are the results from SSMS.
So EXCEPT says give me all of the rows in the first table and then delete from that result set all of the matching rows in the second table. What if there was a row in the second table that was not in the fists table? That row simply gets ignored and is not part of the result set.
Duplicates
What if we have duplicates in the first table. We have added some data to the first table. There is a duplicate, namely HR, Bryn. Notice that the results eliminate the duplicate rows from the first table.
1 | select * from tblDepartment; |
7 | delete from tblDepartmentNew where Department = 'HR'; |
9 | select * from tblDepartmentNew |
11 | select * from tblDepartment |
13 | select * from tblDepartmentNew; |
18 | select * from tblDepartment |
20 | select * from tblDepartmentNew |
23 | into tblDepartmentResult |
Here are the results from SSMS.
