Intersection in SQL

[Pityboy]

I have two tables with same number of columns and similar datatypes say
t1(c1,c2,c3,c4)
and t2(c1,c2,c3,c4)

i read about intersect funcion in sql books online..
intersect({[1],[2],[3]},{[2],[3],[4]}) will return 1 and 4

so i want to have the same results with my queries?

say
intersect({select c1,c2,c3,c4 from t1},
{select c1,c2,c3,c4 from t2})

but it does n't work...

Is there any other way to get the result of the intersection..??

Sri Prabu Vontlin

 

[MalcolmW]

Standard ANSI SQL for intersect is
SELECT C1, C2, C3, C4
FROM T1
INTERSECT
SELECT C1, C2, C3, C4
FROM T2
SQL Server has limited set operator support - just UNION and UNION ALL. Assuming C1 is the primary key, SQL Server does it with
SELECT C1, C2, C3, C4
FROM T1
WHERE EXISTS
(SELECT C1
FROM T2
WHERE T1.C1 = T2.C2)
SQL Server does not support EXCEPT (known as MINUS in Oracle), so to get simulate EXCEPT, change the WHERE EXISTS to WHERE NOT EXISTS.
The words INTERSECT and EXCEPT are used in T-SQL, but as set functions, not set operators. UNION is used both as set operator and as a set function, to add to the confusion.

[sig][/sig]