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

August 7, 2008

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

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,&nbsp;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)

SQL Joins, SQL Scripts

SQL SERVER – Query to Find Column From All Tables of Database

SQL SERVER – 2008 – Released To Manufacturing Available

Do Index Reorganization Update Statistics? – Interview Question of the Week #196

October 21, 2018

SQL SERVER 2016 – Find Expiry Date of Developer Edition

July 13, 2016

SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info

March 28, 2008

54 Comments. Leave new

Raghav
October 5, 2011 12:59 am
Great Share. Thanks a lot.
Reply
Rick Jackson
December 30, 2011 1:41 am
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
Sanya
April 12, 2012 4:21 pm
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
Suvendu
April 24, 2012 5:16 pm
Never used it. Just learned it now.
Thanks !!!
Reply
Kubilay
July 16, 2012 8:52 pm
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
erte
February 18, 2013 10:47 pm
good…. new one…
Reply
vyshu
February 19, 2013 6:10 pm
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
Tejas
March 3, 2013 6:58 pm
Hi. Is EXCEPT same as NOT IN? OR Whats the difference?
Reply
Dushyen
April 18, 2013 5:32 pm
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
- KukoCL
  June 30, 2017 2:50 am
  Pass it to a temp table.
  select * into #tmp_difference 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
somesh
May 2, 2014 11:43 pm
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
udhayakumar
July 16, 2014 4:41 pm
Awesome post !
Thanks a lot !
Reply
thafreakinpope
January 20, 2015 12:07 am
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
jm
October 26, 2015 5:11 pm
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
SR
March 9, 2016 12:29 pm
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
- Pinal Dave
  March 13, 2016 5:46 pm
  I am not able to understand the question.
  Reply
harold vargas
August 5, 2018 10:59 pm
thanks, for your help
Reply