Re: Selecting rows from one table that are not in the other
- From: Gert-Jan Strik <sorry@xxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 21 Apr 2006 20:46:06 +0200
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
- References:
- Prev by Date: Re: Trigger Question
- Next by Date: Re: Trigger Question
- Previous by thread: Re: Selecting rows from one table that are not in the other
- Next by thread: SQL and Terminal server on 2 different PC's
- Index(es):
Relevant Pages
|
