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
Great Share. Thanks a lot.
Can you use the except when comparing on 2 different database on same server?
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.
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.
Never used it. Just learned it now.
Thanks !!!
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
good…. new one…
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
Hi. Is EXCEPT same as NOT IN? OR Whats the difference?
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
Pass it to a temp table.
select * into #tmp_difference from Query1
exept
select * from Query2
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
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
Awesome post !
Thanks a lot !
Thanks, Pinal Dave! This post was exactly what I needed!
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.
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'
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
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
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
I am not able to understand the question.
thanks, for your help