One of the JR. Developer asked me a day ago, does SQL Server has similar operation like MINUS clause in Oracle.
Absolutely, EXCEPT clause in SQL Server is exactly similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.
Let us see that using example below. First create table in SQL Server and Oracle.
CREATE TABLE EmployeeRecord (EmpNo INT NOT NULL, EmpName VARCHAR(10), EmpPost VARCHAR(9), ManagerID INT, Salery INT, COMM INT, DeptNO INT); INSERT INTO EmployeeRecord VALUES (7369, 'SMITH', 'CLERK', 7902, 800, NULL, 20); INSERT INTO EmployeeRecord VALUES (7499, 'ALLEN', 'SALESMAN', 7698, 1600, 300, 30); INSERT INTO EmployeeRecord VALUES (7521, 'WARD', 'SALESMAN', 7698, 1250, 500, 30); INSERT INTO EmployeeRecord VALUES (7566, 'JONES', 'MANAGER', 7839, 2975, NULL, 20); INSERT INTO EmployeeRecord VALUES (7654, 'MARTIN', 'SALESMAN', 7698, 1250, 1400, 30); INSERT INTO EmployeeRecord VALUES (7698, 'BLAKE', 'MANAGER', 7839, 2850, NULL, 30); INSERT INTO EmployeeRecord VALUES (7782, 'CLARK', 'MANAGER', 7839, 2450, NULL, 10); INSERT INTO EmployeeRecord VALUES (7788, 'SCOTT', 'ANALYST', 7566, 3000, NULL, 20); INSERT INTO EmployeeRecord VALUES (7839, 'KING', 'PRESIDENT', NULL, 5000, NULL, 10); INSERT INTO EmployeeRecord VALUES (7844, 'TURNER', 'SALESMAN', 7698, 1500, 0, 30); INSERT INTO EmployeeRecord VALUES (7876, 'ADAMS', 'CLERK', 7788, 1100, NULL, 20); INSERT INTO EmployeeRecord VALUES (7900, 'JAMES', 'CLERK', 7698, 950, NULL, 30); INSERT INTO EmployeeRecord VALUES (7902, 'FORD', 'ANALYST', 7566, 3000, NULL, 20); INSERT INTO EmployeeRecord VALUES (7934, 'MILLER', 'CLERK', 7782, 1300, NULL, 10); SELECT * FROM EmployeeRecord;
Now run following query in SQL Server:
SELECT EmpNo, EmpName FROM EmployeeRecord WHERE Salery > 1000 EXCEPT SELECT EmpNo, EmpName FROM EmployeeRecord WHERE Salery > 2000 ORDER BY EmpName;
Now run following query in Oracle:
SELECT EmpNo, EmpName FROM EmployeeRecord WHERE Salery > 1000 MINUS SELECT EmpNo, EmpName FROM EmployeeRecord WHERE Salery > 2000 ORDER BY EmpName;
You will find that both the query will return you same results.
Drop the table in SQL Server and Oracle as we are done with example.
DROP TABLE EmployeeRecord;
This proves that EXCEPT clause in SQL Server is same as MINUS clause in Oracle.
Here is another interesting article on the subject of difference between EXCEPT operator and Not In.
Reference : Pinal Dave (https://blog.sqlauthority.com)
54 Comments. Leave new
Pinal,
What are your recommendations for when to use EXCEPT, NOT EXISTS, and NOT IN? They all seem to provide similar functionality.
Thanks,
Dan
“Except” performs the set difference operation, “not in” checks for non-membership, and “not exists” determines whether a table(or generated select query) has any records. If that doesn’t make any sense then look up some set theory.
Hello, sql authority — I have a puzzle for you…I wrote the following code:
select id
from records
EXCEPT
select id
from records
where cols = -1
WHICH RETURNS THIS ERROR MESSAGE:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘EXCEPT’.
I’m using Microsoft sql server management studio express — the same as in the example you have above. Do you see anything wrong with this query as it appears above? I can’t! I had “distinct” in the select statements, but took them out, because the documentation I found online says that it works on distinct rows within the except function.
Help!
Donna Bursey
Looks fine to me. I suspect you’re not running SQL 2008+. It appears to be good syntax.
Hmm, strange statement. I have the same question in my mind as Dan above.
@Dan, @elfy,
From my experience I would use EXCEPT, NOT EXISTS and NOT IN in the order they are mentioned. EXCEPT (Best performance) and NOT IN (Worst performance).
Kind Regards,
Pinal Dave
pinal, but I see query cost(relative to batch) is more for except than not in. how can we say that not in is worst performance.
Thanks, Venkat.
I was really happy to find INTERSECT & EXCEPT in SQL 2005, similiar to Oracle’s MINUS is quite cool
Although I think I’ve tried it, I think
NOT EXISTS >> EXCEPT (does it not Cartesian than filter?) >> NOT IN (bad bad bad)
It could also depend on the data sets, but I’ve recommended everyone I know to code with NOT EXISTS in mind
this worked like a charm!
much, much faster than NOT IN…
regards,
Giovanna
SIR
i have creataed a table in which i have inserted different students marks
now i want to write a program(may be it is done through function or procedure) in oracle sql so that user is prompted to enter the top values i mean if user want to access top 10 students marks so he just need to write 10 in the box and result should be displayed similarly for top 15 in short from top 1- top 15
just like if i write the quuery
select * from stu where stu_marks=’&Entertop’
so user is prompted to enter top values but i want to write a program for it in oracle sql(pl sql)
i shall be thankful to you
Hi Pinaldave,
below queries also get same out put. So, which is the best perpromance. Is it EXCEPT more performance then below queries.
SELECT EmpNo, EmpName,Salery
FROM EmployeeRecord
WHERE Salery > 1000 and Salery < 2000
ORDER BY EmpName;
SELECT EmpNo, EmpName,Salery
FROM EmployeeRecord
WHERE Salery between 1000 and 2000
ORDER BY EmpName;
Thank you,
Gangadhar.
What does “exactly similar” mean ? It two things are the same, or identical, they are not similar. They are alike.
Hello, Mr. Dave
I am working as dba in Faragamara software company in Iran,
And want to thank you because of your trainings and solutions of SQL.
With best wishes
Hello Mr. Dave,
Is it possible to capture the results from the EXCEPT query and put them in a temporary table.
Thanks,
Joe
A derived table should be able to do that. SELECT * into #d from (select * from table1 except select * from table1a) a
Hello Joe,
Yes, of course. Are you facing any issue?
Regards,
Pinal Dave
Hi Sir,
We can use between, then >,< Operator also.
it is also given the same output.
which one is better performance.Please suggest me
Thank you.
Hi Uma,
operators will give you the best results only when you have only one source table where you are trying to find the results for employees falling under the salary category 1000><2000. But consider the case, where you have a datawarehousing target which maintains the history, and before loading in to that table you wanna verify the records which are all new, in this case minus or except will work.
Hope am clear. Please let me know if am wrong so that I can understand it in a best way from you.
Regards
Mani
What should i do if i want to return only the columns that do not match for 2 tables ?
Any help is appreciated.
Yes.
using sql server use two level of complement using the except operator
and in oracle there is alot of option.
using minus or intersection operator.
may this will be helpfull.
in addition to what khan is saying, you may want to use for example:
tableA Except tableB
to return data in tableA that don’t match with the data in tableB. Similarly, you can use:
tableB Except tableA
to return data in tableB that don’t match with the data in tableA.
can we use EXCEPT clause in Oracle 10g? Though they say that EXCEPT in SQL server is the same as ORACLE’s MINUS, in a situation where LHS data set has a duplicate record and RHS data set has one instance of the same record, then MINUS will not work as it will not even bring this up.
Please help.
Regards
Balaji Raja
Hi Sir
i want Near operator in Sql Server like oracle
example
‘near((lion, tiger), 10)
Regards
Yakub Tamboli
Just wanted to clarify your terminology: both EXCEPT (and INTERSECT) should be referred to as “operator”, and not as “clause”.
A definition of a clause is “group of [of words] containing subject and predicate”; therefore WHERE, GROUP BY and ORDER BY are rightfully classified as clauses.
An operator is defined as “something … that … performs a mathematical or logical operation” (Merriam Webster definition). Therefore EXCEPT is an operator performing logical operation upon two sets of data returned by the queries.
thanks,
-alex
Hi Pinal Dave,
I have a query regarding indexed varchar field,
The situation is: currently I have a table called detalleplanilla
that contains a field called expenses and I need to get information.
In the field expenses I have this information:
EmpID | Date1 | Date2 | Expenses
————————————————– ————————————————– ———————+
1 | 2011-01 – 01 | 1/15/2011 |, Savings 1500.00, Loan 5000.00 |
————————————————– ————————————————– ———————+
1 | 2011-01 – 16 | 1/31/2011 |, Savings 1500.00, Loan 5000.00 |
————————————————– ————————————————– ———————+
n + … | … | … | … |
————————————————– ————————————————– ———————+
Note: Expenses are separated by “, ”
I need a query that identifies you such as:
Total Savings of X employee in January or range date.
The answer would be = Savings 3500.00
What can I do?.
I need your help.
Thanks for your time.
Ronald Garcia
HI Ronald
Try this,…
CREATE TABLE EMP_Demo
(EMP_PAY VARCHAR(20),
EMP_NAME VARCHAR(20),
PAY_SCALE VARCHAR(20));
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES (‘Alpha,7009’)
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES (‘Beta,9909’)
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES (‘Asdf,16000.7’)
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES (‘Abcd,6060.8’)
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES (‘Qwerty,14000’)
update Emp_Demo
set Emp_Name=left(EMP_PAY,charindex(‘,’,EMP_PAY)-1)
update Emp_Demo
set Pay_Scale=right(EMP_PAY,len(EMP_PAY)-(charindex(‘,’,EMP_PAY)))
cheers !!!
Hi,
What if i have 2 tables and in each table there is 5 columns, but i want that my EXCEPT will be only on 3/5 columns(til here every thing is easy).
Now i want that the resoult that i will get will contains the all 5 columns and not only the 3 columns that the EXCEPT base on.
Is there a way to do it without using INNER JOIN?
TNK.
Dear pine.
1 thnks that you are there for help.
i have two CDR files and i want to compare the values of the both different CDR files that have the same no and types of column, some how all the values are the same but there is a difference in the durration column.
how can i get comparison report in sql server through query.
Please help