Difference between except and not in sql server
We will use the following 2 tables for this example.
![]() | ![]() |
1. Except filters duplicates and returns only DISTINCT rows from the left query that aren’t in the right query’s results, where as NOT IN does not filter the duplicates.
Select Id, Name, Gender From TableA Except Select Id, Name, Gender From TableB
Result:
![except operator result](https://2.bp.blogspot.com/-ungwFYLGoow/Ves55QHguKI/AAAAAAAAd54/vIyCLZ94gX0/s1600/except%2Boperator%2Bresult.png)
Now execute the following query. Notice that the duplicate rows are not filtered.
Select Id, Name, Gender From TableA Where Id NOT IN (Select Id from TableB)
Result:
![sql server not in example](https://4.bp.blogspot.com/-hRmPhzO-D9c/Ves7IMgxosI/AAAAAAAAd6E/htNAZhPmqp0/s1600/sql%2Bserver%2Bnot%2Bin%2Bexample.png)
2. EXCEPT operator expects the same number of columns in both the queries, where as NOT IN, compares a single column from the outer query with a single column from the subquery.
In the following example, the number of columns are different.
Select Id, Name, Gender From TableA Except Select Id, Name From TableB
The above query would produce the following error.
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
NOT IN, compares a single column from the outer query with a single column from subquery.
In the following example, the subquery returns multiple columns
Select Id, Name, Gender From TableA Where Id NOT IN (Select Id, Name from TableB)
Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.