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

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

    Reply
  • Great Share. Thanks a lot.

    Reply
  • Can you use the except when comparing on 2 different database on same server?

    Reply
  • Michael Pindrik
    February 10, 2012 3:25 am

    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.

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

    Reply
  • Never used it. Just learned it now.
    Thanks !!!

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

    Reply
  • good…. new one…

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

    Reply
  • Hi. Is EXCEPT same as NOT IN? OR Whats the difference?

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

    Reply
  • Amit Kumar Dhiman
    January 29, 2014 9:17 am

    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

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

    Reply
  • Awesome post !
    Thanks a lot !

    Reply
  • Thanks, Pinal Dave! This post was exactly what I needed!

    Reply
  • Ankit Nighojkar
    August 11, 2015 6:47 am

    Hi Pinal- I want to get all records from Table A which are not present in either table B, or table C or table D. And for those records I need to put multiple inner joins to get corresponding matching values. I’ve been trying to get that but got stuck with approach. A draft of query is as below. Could you please help me here-
    Part 1. Get all the IndividualKey which are not in other three tables-

    select IndividualKey from FactAAMSignals
    Except
    select IndividualKey from IDSYNC_CMS_AAM_TGT
    Except
    select Individualkey from IDSYNC_IND_AAM_TGT
    Except
    select Individualkey from IDSYNC_ORD_AAM_TGT

    Part 2. For output of Part 1, get corresponding matching records-
    SELECT D.Name_ID ,D.FirstName, D.LastName, D.GenderCode, MN.IndividualKey, MN.Location_ID,
    DL.Location_ID, DE.EmailAddress, AddressLineName1, DL.AddressLineName2, DL.CityName, DL.StateCode, DL.ZipCode_USAREA,DL.Zip4AddonCode_USAREA
    FROM [IBM_MDM].[dbo].[DimName] D Inner join dbo.MDMNameLocationXref MN
    ON D.Name_ID=MN.Name_ID
    Inner Join DimLocation DL
    ON MN.Location_ID=DL.Location_ID
    Inner Join DimEmail DE
    ON D.Name_ID=DE.Name_ID

    Now how I can club both the parts (1 & 2) into a single query to get final output. Your help would be highly appreciated.

    Reply
  • select od=1,nome=’Saldo de ‘+cast( 2013 as char(10)),
    – isnull (sum((case when account=’311001′ then Debit-Credit else 0 end)),0) as share,
    -isnull (sum((case when account=’311002′ then Debit-Credit else 0 end)),0) as Suplementary,
    -isnull(sum((case when account=’392001′ then Debit-Credit else 0 end)),0) as Legal,
    -isnull(sum((case when account=’392003′ then Debit-Credit else 0 end)),0) as Investment,
    -isnull(sum((case when account=’391001’ then Debit-Credit else 0 end)),0) as Accumulated,
    -isnull(sum((case when account in (‘311001′,’311002′,’392001′,’392003′,’391001’) then Debit-Credit else 0 end)),0) as Total
    from JDT1
    –where RefDate<= '2013-06-30'
    where year(RefDate) = 2013 and MONTH (RefDate)='06' and DAY (RefDate)='30'

    Reply
  • PushkaraGowtham.K
    January 20, 2016 6:14 pm

    Hi Dave,

    I have 2 tables which is around 1TB size data. Now I want insert the data which is newly created in the table1. So here can we use except query? or can you suggest is there any way to insert the data new data into the table? ASAP

    Reply
  • PushkaraGowtham.K
    January 20, 2016 6:21 pm

    Hi Dave,

    I have 2 tables(Table 1 & Table 2) each one is around 1TB size of data. Now I want to load the latest records which are newly created/inserted by comparing the table 2. So in this case can we use “Except” to compare and insert the new records into the target table? or suggest me is there any other way to achieve this? Can you please help me ASAP.

    Thanks,
    PushkaraGowtham.K

    Reply
  • I HAVE 2 TABLE EMP1 AND EMP2

    EMP1
    1- JOHN
    2- SMITH
    3 – MORTUAN

    EMP2
    1- JOHNBHAI
    2- SMITH
    3 – MORTUANKUMAR

    I HAVE COMPARANIG EMP1 AND EMP2 AND HOW TO GET ONLY OUT PUT 2 – SMITH , THERE RECORD ARE SKIPING TO COMPARE BHAI , KUMAR

    Reply

Leave a Reply

Menu