|
|
Posted by Gert-Jan Strik on 04/21/06 21:46
You can choose one of the following syntaxes:
1) OUTER JOIN
-------------
SELECT table1.*
FROM table1
LEFT OUTER JOIN table2
ON table2.keycolumn = table1.keycolumn
WHERE table2.keycolumn IS NULL
2) NOT EXISTS
-------------
SELECT *
FROM table1
WHERE NOT EXISTS (
SELECT *
FROM table2
WHERE table2.keycolumn = table1.keycolumn
)
3) NOT IN
---------
SELECT *
FROM table1
WHERE keycolumn NOT IN (
SELECT keycolumn
FROM table2
)
Personally, I prefer syntax 2.
If the query is simple, then these syntaxes will most likely result in
the same query plan. If the query is more complex, the query plans can
differ, based on the syntax. Then, syntax 2 is still my favorite.
However, if table 2 has many duplicate values, you might want to
consider syntax 3. If SQL Server uses too much parallellism and
CPU-cycles then you could try syntax 1.
HTH,
Gert-Jan
Zvonko wrote:
>
> hi!
>
> I have two tables with same structure. I need to run a select query that
> will return only the rows
> from one table that are not in the other.
>
> In MySQL it would be
> select * from table1 MINUS select * from table2.
> but in MsSql I can not find the apropriate way to do this.
> Any Help?
>
> Zvonko
Navigation:
[Reply to this message]
|