The EXCEPT operator returns the rows that are only in the first result set but not in the second. EXCEPT introduced from SQL Server 2005.
The NOT IN
returns all rows from returned from by first result set which does not exist in the second select statement
When we combine two queries using EXCEPT clause, it will returns distinct rows from the first SELECT statement that are not returned by the second one.
EXCEPT clause works the same way as the UNION operator of SQL and MINUS clause in Oracle.
The syntax of EXCEPT clause is as follow
SELECT column1[, column2 ] FROM table1 [, table2 ] [WHERE condition]
EXCEPT
SELECT column1 [, column2 ] FROM table1 [, table2 ]
[WHERE condition]
The difference between EXCEPT and NOT IN clause is EXCEPT operator returns all distinct rows from the rows returned by first select statement which does not exist in the rows returned by second select statement. On the other hand “NOT IN” will return all rows from returned by first select statement which does not exist in the rows returned by second select statement.
Below is example for same..
— Lets Create two sample tables ( I am creating here table variables)
Declare
@VirendraTestTable1
table (id
int,
course
varchar(50)
);
Declare
@VirendraTestTable2
table (id
int,course
varchar(50)
);
— Insert Some sample date to table @VirendraTestTable1
Insert
into
@VirendraTestTable1
values(1,‘ASP .NET’),(2,‘SQL SERVER’),(2,‘SQL SERVER’),(3,‘FOXPRO’),(3,‘FOXPRO’)
— Insert Some sample date to table @VirendraTestTable2
Insert
into
@VirendraTestTable2
values(1,‘ASP .NET’),(2,‘SQL SERVER’),(2,‘SQL SERVER’)
— Run query with EXCEPT operator, Only distinct rows will return
Print
‘EXCEPT output’
Select
id,course
from
@VirendraTestTable1
except
Select
id,course
from
@VirendraTestTable2
— Run query with NOT IN operator, duplicate rows will exist in the result
Print
‘NOT IN output’
Select
id,course
from
@VirendraTestTable1
Where
id
not
in
(Select
id
from
@VirendraTestTable2
)
Hence conclusion is ,
EXCEPT is defined in terms of duplicates based on distinctness, and for example (1 is distinct from 1) is false, (1 is distinct from NULL) is true and (NULL is distinct from NULL) if false.
