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

There are more then one way to do this. Heres one:

select <column list>
from
TableA
left join TableB on tableA.col1 = tableB.col1
where
tableB.col1 is null

You could also use NOT EXISTS and so on..


MC



"Zvonko" <zvonko_NOSPAM_ (AT) velkat (DOT) net> wrote

Zvonko (zvonko_NOSPAM_ (AT) velkat (DOT) net) writes:
I don't know what the semantics are of the MINUS operator in MySQL, but in
SQL 2005 you can use EXCEPT instead. (EXCEPT is not available in SQL
2000.) EXCEPT works on the entire result set, so if id 78 appears in both
tables, but the rest of the data is different, you get both rows.

If you want to see ids in table1 that are not in table2, the syntax is:

SELECT ...
FROM table1 a
WHERE NOT EXISTS (SELECT *
FROM table2 b
WHERE a.id = b.id)

This is also what you need to use on SQL 2000 to implement EXCEPT.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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: