Interesect Command(SQL Server 2005)

One of new feature of SQL Server 2005 is except and intersect commands.
Lets look how it can be useful.





CREATE TABLE #temp (

id INT,

data VARCHAR(100))



INSERT INTO #temp

VALUES (1,'a')


INSERT INTO #temp

VALUES (1,'b')


INSERT INTO #temp

VALUES (2,'a')


INSERT INTO #temp

VALUES (2,'b')


CREATE TABLE #temp1 (

id INT,

data VARCHAR(100))


INSERT INTO #temp1

VALUES (1,'a')


INSERT INTO #temp1

VALUES (1,'b')


INSERT INTO #temp1

VALUES (3,'a')


INSERT INTO #temp1

VALUES (3,'b')


Now we want matching row from #temp and #temp1

In SQL 2000 we can do this by this way


SELECT *

FROM #temp t

WHERE EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data)


In SQL 2005 we can do it easily by intersect


SELECT *

FROM #temp

INTERSECT

SELECT *

FROM #temp1