• 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.

NOT IN is defined in terms of equality, and for example, (1=1) is true,(1=NULL) is unknown and (NULL=NULL) is unknown.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s