SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle

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.

SQL SERVER - EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle ExceptMinus

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)

,
Previous Post
SQL SERVER – Query to Find Column From All Tables of Database
Next Post
SQL SERVER – 2008 – Released To Manufacturing Available

Related Posts

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

    Reply
    • “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.

      Reply
  • 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

    Reply
  • Hmm, strange statement. I have the same question in my mind as Dan above.

    Reply
  • @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

    Reply
    • 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.

      Reply
  • 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

    Reply
  • this worked like a charm!
    much, much faster than NOT IN…
    regards,
    Giovanna

    Reply
  • 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

    Reply
  • Gangadhar Naidu
    July 16, 2009 11:44 am

    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.

    Reply
  • What does “exactly similar” mean ? It two things are the same, or identical, they are not similar. They are alike.

    Reply
  • 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

    Reply
  • Hello Mr. Dave,

    Is it possible to capture the results from the EXCEPT query and put them in a temporary table.

    Thanks,

    Joe

    Reply
    • A derived table should be able to do that. SELECT * into #d from (select * from table1 except select * from table1a) a

      Reply
  • Hello Joe,

    Yes, of course. Are you facing any issue?

    Regards,
    Pinal Dave

    Reply
  • 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.

    Reply
    • Manikandan Dinakaran
      February 24, 2011 3:12 pm

      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

      Reply
  • What should i do if i want to return only the columns that do not match for 2 tables ?

    Any help is appreciated.

    Reply
    • 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.

      Reply
      • 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

    Reply
  • Hi Sir

    i want Near operator in Sql Server like oracle
    example
    ‘near((lion, tiger), 10)

    Regards

    Yakub Tamboli

    Reply
  • 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

    Reply
  • 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

    Reply
    • 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 !!!

      Reply
  • 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.

    Reply
  • 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

    Reply

Leave a Reply

Menu