Except command -SQL Server 2005

Except command can be used when we want rows from table1 which are not in table2.
This command is supported in SQL 2005 and later versions.
Let see an example for this

CREATE TABLE #temp (


id   INT,
data VARCHAR(100),
code INT)
INSERT INTO #temp
VALUES     (1,'a',1)
INSERT INTO #temp
VALUES     (1,'b',2)
INSERT INTO #temp
VALUES     (2,'a',1)
INSERT INTO #temp
VALUES     (2,'b',2)
CREATE TABLE #temp1 (
id   INT,
data VARCHAR(100),
code INT)
INSERT INTO #temp1
VALUES     (1,'a',1)
INSERT INTO #temp1
VALUES     (1,'b',2)
INSERT INTO #temp1
VALUES     (3,'a',1)
INSERT INTO #temp1

VALUES (3,'b',2)

Now we want rows from #temp which are not in #temp1

In SQL 2000 we can do this by matching all columns

SELECT *


FROM   #temp t
WHERE  NOT EXISTS (SELECT *
FROM   #temp1 t1
WHERE  t.id = t1.id
AND t.data = t1.data

AND t.code = t1.code)

In SQL 2005 we can do it easily by except

SELECT *


FROM   #temp
EXCEPT
SELECT *

FROM #temp1

This entry was posted on June 17, 2009 at 5:06 am and is filed under SQL \ TSQL ....:-). You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “Except command -SQL Server 2005”

PT Says:
July 22, 2009 at 8:02 am | Reply
I know the idea of the post is to demonstrate EXCEPT, but as far as the NOT EXISTS is concerned, since your not interested in the return value of the nested SELECT, you could changed it to …NOT EXISTS (SELECT 1 FROM #temp1…Maybe nitpicking. Do you have any idea on how these two methods compare performance-wise ?
Amish Manubhai Shah Says:
July 22, 2009 at 9:12 am | Reply
Hi PT It will not affect performance.If you look at execution plan for either not exists (select * from #temp….)or not exists (select 1 from #temp….)both are same. There is similar question for count performance.select count(*) from table1or select count(1) from table1Many think that count(1) is faster but actually both are same. Ideal way to find is look at execution plan and get statistics io for the query. Cheers Amish Shah
PT Says:
July 22, 2009 at 11:50 am | Reply
Yes, your right.I found the following on SQL Server 2000 : http://msdn.microsoft.com/en-us/library/aa213271(SQL.80).aspxMaybe there was a reason for using 'SELECT 1' in pre-2000 versions of SQL Server or in other types of databases, but as of 2000 there appears to be no benefit.

Lets SQL