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.

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

43 thoughts on “SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle

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

      Like

  1. 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

    Like

  2. @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

    Like

  3. 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

    Like

  4. 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

    Like

  5. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 7 Journey to SQL Authority with Pinal Dave

  6. 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.

    Like

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

    Like

  8. 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.

    Like

    • 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

      Like

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

      Like

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

        Like

  9. 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

    Like

  10. 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

    Like

  11. 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

    Like

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

      Like

  12. 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.

    Like

  13. 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

    Like

  14. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31 Journey to SQLAuthority

  15. Hello sir
    i have a query…
    i want only selected column from all of the colum from the same table
    i dont want to use the required column names from table
    can you suggest me the solution to that.
    ex.
    if there is a table having 30 fields
    i want 28 field among them
    then how can i fetch those 28 colunm
    without using
    Select (28 column name) from table.

    pls give the solution to that..

    Like

  16. I think the data returned by two SELECTs has match exactly in order for EXEPT to work. I was troubleshooting somebody’s code where EXCEPt did not work because order of fields in the second SELECT did not match the order of field in the first SELECT.

    Like

  17. I want to delete duplicate data . But i am not able to do that using except keyword.

    select * from table1
    except
    select distinct * from table1.

    No error occurred. Please help me with the same.

    Like

  18. Hi

    Has anybody have any tips on tuning the EXCEPT statement in SQL Server 2008. I am going to compare sets of 100K rows with (A EXCEPT B) fashion. It is very slow. Any tips on how to tune it.

    Many thanks

    Kubilay

    Like

  19. Hi Abhijit, is your question
    (Question – Hello sir
    i have a query…
    i want only selected column from all of the colum from the same table
    i dont want to use the required column names from table
    can you suggest me the solution to that.
    ex.
    if there is a table having 30 fields
    i want 28 field among them
    then how can i fetch those 28 colunm
    without using
    Select (28 column name) from table.

    pls give the solution to that..)
    answered? I also have similar requirement.
    Can anyone please reply on this.

    @Abhijit – i got a solution for this but not so apt. please find below:
    /* Get the data into a temp table */
    SELECT * INTO #TempTable
    FROM YourTable
    /* Drop the cloumns that are not needed */
    ALTER TABLE #TempTable
    DROP COLUMN ColumnToDrop
    /* Get results and drop temp table */
    SELECT * FROM #TempTable
    DROP TABLE #TempTable

    Like

  20. I am using except in SQL Query. i want to retain the duplicates present in table 1 which are not present in query 2
    select * from Query1
    exept
    select * from Query2

    Like

  21. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  22. The below query also returns the same result, and found no issues in performance
    select x.* from
    (SELECT EmpNo, EmpName, Salery
    FROM temp.EmployeeRecord
    WHERE Salery > 1000)x
    left join
    (SELECT EmpNo, EmpName, Salery
    FROM temp.EmployeeRecord
    WHERE Salery > 2000) y
    on x.EmpNo = y.EmpNo
    where y.EmpNo is null
    order by EmpName

    Like

  23. Hi,

    My name is somesh. You blog is exceptional. I have a question related to SQL Server connecting for Linux.

    When I query a table (returning multiple values) and assign to a variable. I am only getting one value (the first row). How do I hold all the values in an array for further processing?

    Thanks
    Somesh

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s