SQL Tips by Namwar Rizvi

May 23, 2007

Difference between EXCEPT and NOT IN Operator

Filed under: Uncategorized — namwar @ 7:53 PM

SQL Server 2005 has introduced a new operator called “EXCEPT”. It is a good shortcut for Distinct Not In queries. EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table. On the other hand “NOT IN” will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result. Following code demonstrates the behavior of these two operators:

–Disbale intermediate informative messages
Set Nocount on
go

—Create two sample tables
Declare @m_TestTable table ( id int, name varchar(50) )
Declare @m_TestTable2 table ( id int, name varchar(50) )

–Fill first table with sample data
Insert into @m_TestTable values(1,‘BBC’)
Insert into @m_TestTable values(2,‘CNN’)
Insert into @m_TestTable values(2,‘CNN’)
Insert into @m_TestTable values(3,‘FoxNews’)
Insert into @m_TestTable values(3,‘FoxNews’)

–Fill second table with sample data
Insert into @m_TestTable2 values(1,‘BBC’)

–Run query with EXCEPT operator, Only distinct rows will return
Select id,name from @m_TestTable
except
Select
id,name from @m_TestTable2

–Run query with NOT IN operator, duplicate rows will exist in the result
Select distinct id,name from @m_TestTable
Where id not in
( Select id from @m_TestTable2 )

Advertisements

1 Comment »

  1. There was a function in SQL Server2000 “Except” e.g.
    Except({Canada, [British Columbia], Mexico, [British Columbia], USA, Washington}, {Canada, Mexico, California})
    but its generating the access violation error, please help,
    Fahim Ullah Khan

    Comment by Fahim Ullah Khan — May 30, 2007 @ 10:27 AM | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: