The Except Operator returns any distinct values from the left query that are not also found on the right query.
The Except operator evaluates the output of two query expressions and returns the difference between the results. The result set contains all rows returned from the first query expression except those rows that are also returned from the second query expression.
Syntax:-
SELECT [TableName1].[ColumnName1]
FROM TableName1
EXCEPT
SELECT [TableName2].[ColumnName2]
FROM TableName2
Example:-
SELECT emp_no FROM Employee
WHERE dept_no = ‘d3’
EXCEPT
SELECT emp_no FROM Designation WHERE enter_date > ‘01.01.2008’
LET it explain with Tables
Employee Table
| emp_no | emp_fname | emp_lname | dept_no |
| 25348 | Matthew | Smith | d3 |
| 10102 | Ann | Jones | d3 |
| 18316 | John | Barrimore | d1 |
| 29346 | James | James | d2 |
| 9031 | Elsa | Bertoni | d2 |
| 2581 | Elke | Hansel | d2 |
| 28559 | Sybill | Moser | d1 |
Designation Table
| emp_no | project_no | job | enter_date |
| 10102 | p1 | Analyst | 2006.10.1 |
| 10102 | p3 | Manager | 2008.1.1 |
| 25348 | p2 | Clerk | 2007.2.15 |
| 18316 | p2 | null | 2007.6.1 |
| 29346 | p2 | null | 2006.12.15 |
| 2581 | p3 | Analyst | 2007.10.15 |
| 9031 | p1 | Manager | 2007.4.15 |
| 28559 | p1 | null | 2007.8.1 |
| 28559 | p2 | Clerk | 2008.2.1 |
| 9031 | p3 | Clerk | 2006.11.15 |
| 29346 | p1 | Clerk | 2007.1.4 |
Result from Except query
| Emp_no |
| 10102 |
| 25348 |
EXCEPT operator in Sql Server works in the same way of NOT IN.
EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.
