Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Minus vs Except Difference in ORACLE/SQL Server

Tags:

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

like image 224
jhash Avatar asked Apr 05 '11 20:04

jhash


People also ask

What is MINUS in Oracle SQL?

In Oracle, MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement has a dataset and the MINUS operator returns all documents from the first dataset and then removes all documents from the second dataset.

What is the difference between MINUS and not exists in Oracle?

If both tables a roughly the same size, then MINUS might be faster, particularly if you can live with only seeing fields that you are comparing on. If you wanted to see if you had retirees that were not in the employee table, then you could use either MINUS or NOT EXISTS (or even NOT IN, but you didn't ask).

Is there a MINUS in SQL Server?

The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query.

What is equivalent to MINUS in SQL Server?

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.


2 Answers

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)
like image 159
RichardTheKiwi Avatar answered Sep 18 '22 09:09

RichardTheKiwi


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> 
like image 26
JNK Avatar answered Sep 19 '22 09:09

JNK