Minus vs Except Difference in ORACLE/SQL Server
Solution 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)
Solution 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>
Related videos on Youtube
jhash
Updated on March 26, 2020Comments
-
jhash about 4 years
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
-
GriffeyDog about 13 yearsMy Oracle MINUS doesn't work that way. This returns no rows: select 1 from dual where 1=2 minus select 2 from dual;
-
jhash about 13 years@GriffeyDog,thanks for notification.I noticed that i have an extra union in my query in oracle and that query brings resultset.So No difference between Minus and Except.
-
-
JNK about 13 years@Richard - I was addressing this:
But in SQL Server, EXCEPT returns nothing. What can I do in that case?
I don't claim to know anything about Oracle but he asked a SQL Server specific portion which I answered. -
RichardTheKiwi about 13 yearsOP has misunderstood or misinterpreted some result from the Oracle query. (0-record set) MINUS (x-record set) should not return (x-record set) as claimed, GriffeyDog's comment states the same. Given that, I don't think he would want to perform the query you propose, although it is interesting.
-
JNK about 13 years@richard - I don't disagree if he is looking for consistent results between RDBMS implementations, but I was going on what he told me :) the customer isn't always right but they are always the customer.
-
jhash about 13 yearsThanks for answer jnk but i saw my error.Minus=Except,no problem anymore.