Posted by Erland Sommarskog on 10/02/81 11:45

Zvonko (zvonko_NOSPAM_@velkat.net) writes:
> 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?

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]