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
Now we want rows from #temp which are not in #temp1
In SQL 2000 we can do this by matching all columns
In SQL 2005 we can do it easily by except
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
3 comments:
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 ?
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 table1
or
select count(1) from table1
Many 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
Yes, your right.
I found the following on SQL Server 2000 : http://msdn.microsoft.com/en-us/library/aa213271(SQL.80).aspx
Maybe 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.
Post a Comment