Tags

,


One of the Oracle developer who started working in SQL Server asked me recently if SQL Server has similar operation like Oracle MINUS.

Yes, EXCEPT (in SQL Server) and MINUS (in Oracle) are alike.

It returns all the distinct rows in the first query that are not returned in the second query. The queries that use EXCEPT operator must have same number of columns and the order of the columns should also be the same and their data types should be compatible.

Lets try to understand it better with an example:

— I am going to create two tables for demonstration of this example:

— Creating first table called: Employees_Set1

CREATE TABLE Employees_Set1
(
Id INT, Fname VARCHAR(50), Lname VARCHAR(50)
)

— Inserting dummy records in the table

INSERT INTO Employees_Set1 VALUES ( 1, ‘Jen’ , ‘Ambelang’ )
INSERT INTO Employees_Set1 VALUES ( 2, ‘Alan’ , ‘Eechi’ )
INSERT INTO Employees_Set1 VALUES ( 3, ‘Steve’ , ‘Borders’ )
INSERT INTO Employees_Set1 VALUES ( 4, ‘Adam’ , ‘Carlos’ )
INSERT INTO Employees_Set1 VALUES ( 5, ‘Walter’ , ‘Williams’ )
INSERT INTO Employees_Set1 VALUES ( 6, ‘Madoka’ , ‘Kurosawa’ )
INSERT INTO Employees_Set1 VALUES ( 7, ‘Jane’ , ‘Johnson’ )
INSERT INTO Employees_Set1 VALUES ( 7, ‘Jane’ , ‘Johnson’ )
INSERT INTO Employees_Set1 VALUES ( 8, ‘Hong’ , ‘Annie’ )
INSERT INTO Employees_Set1 VALUES ( 9, ‘Lily’ , ‘Chang’ )
INSERT INTO Employees_Set1 VALUES ( 10, ‘Frank’ , ‘Zhao’ )

— Creating second table called: Employees_Set2

CREATE TABLE
Employees_Set2
(
Id INT, Fname VARCHAR(50), Lname VARCHAR(50)
)

— Inserting dummy records in the table

INSERT INTO Employees_Set2 VALUES ( 1, ‘Jen’ , ‘Ambelang’ )
INSERT INTO Employees_Set2 VALUES ( 2, ‘Alan’ , ‘Eechi’ )
INSERT INTO Employees_Set2 VALUES ( 13, ‘Steve’ , ‘Borders’ )
INSERT INTO Employees_Set2 VALUES ( 4, ‘Adam’ , ‘Carlos’ )
INSERT INTO Employees_Set2 VALUES ( 5, ‘Walter’ , ‘Williams’ )
INSERT INTO Employees_Set2 VALUES ( 11, ‘Nui’ , ‘Wanarasat’ )

Now lets use this EXCEPT operator:

SELECT Fname,Lname FROM Employees_Set1
EXCEPT
SELECT Fname,Lname FROM Employees_Set2
ORDER BY Fname,Lname

From the result set we can see that EXCEPT returns distinct values by comparing the results from two queries.

In the first table we inserted duplicated records with row id=7. But the result set only displays the distinct records.

Lets try another example:

SELECT * FROM Employees_Set1
EXCEPT
SELECT * FROM Employees_Set2
ORDER BY Fname,Lname

In this result set it is also showing row whose ID=3. Because we have given the separate ID to the same FName and LName in the two tables.

Now lets try to do the same thing with NOT IN Clause:

SELECT DISTINCT fname,lname FROM Employees_Set1
WHERE Fname+Lname NOT IN (SELECT Fname+Lname FROM Employees_Set2)
ORDER BY Fname,Lname

Thus , we can get the same result set by NOT IN as well as Except. Although, from the performance perspective we should always give preference to Except over NOT IN.

Thanks

Subhro Saha

Advertisements