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

3 comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts