问题:

MINUS is a SQL set operation that selects elements from the first table and then removes rows that are also returned by the second SELECT statement in Oracle. And in SQL Server, we can use EXCEPT to do same thing.

While migrating my project from oracle to SQL Server, I noticed a difference. If first result set doesn't have records, minus brings result set of second SELECT Statement. But in SQL Server, EXCEPT returns nothing. What can I do in that case? I am migrating my project and want to do same of minus function in SQL Server.

Thanks for your help

回答1:

There is no difference between Oracle MINUS and SQL Server EXCEPT.

They are intended to do the same thing.

http://dotnetguts.blogspot.com/2008/04/minus-keyword-in-sql-server.html
http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/
http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm (search for MINUS operator), read the description, which matches EXCEPT for SQL Server exactly)
Oracle MINUS vs SQL Server EXCEPT (compare docs)

回答2:

This will check for any result set from the first query, then run the except if there is a result. If not it only runs the second query.

IF EXISTS (SELECT NULL
           FROM ... <first query criteria>
           WHERE ...)
BEGIN
    SELECT ... <first query>
    EXCEPT 
    SELECT ... <second query>
END
ELSE
SELECT ... <second query>