Month: July 2017

SQL Server Minus Operator

by
Facebooktwitterredditpinterestlinkedinmail

The MINUS operator is an Oracle operator and does not exist in SQL Server. SQL Server has its own operator that does this called EXCEPT. The usage of these two operators are the same between Oracle and SQL Server.

 
The EXCEPT Operator
The EXCEPT operator will take two queries and only show the records in the first query that do not exist in the second query. Here is an example of how this would work.

SELECT	A.AnimalName,
		AT.AnimalTypeName
FROM	Animal A
INNER	JOIN AnimalType AT ON AT.AnimalTypeID = A.AnimalTypeID

EXCEPT

SELECT	EA.AnimalName,
		AT.AnimalTypeName
FROM	ExtinctAnimal EA
INNER	JOIN AnimalType AT ON AT.AnimalTypeID = EA.AnimalTypeID

 
The above query will show all the animals from the Animal table where the animals are not in the ExtinctAnimal table.

 
 
Reference: https://msdn.microsoft.com/en-us/library/ms188055.aspx