SQL Interview: 14: Set operations using EXCEPT

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

UNION and UNION ALL are commonly used to combine two sets of rows into a single set of rows.

EXCEPT is another set operator.

Can you explain what it does?

Answer:

EXCEPT is used to remove any rows in the first set of rows, if the same rows appear in the second set.

For example, in the code below:

SELECT TradingName FROM Sales.Customers

EXCEPT

SELECT SupplierName FROM Purchasing.Suppliers;

The query returns all the Trading Names for customers unless a supplier also has that same name.

In other database engines (e.g. Oracle), this operator is called MINUS.

Note: For EXCEPT to work, the queries must have the same number of columns, and compatible data types. The values must also be “comparable”. For example, with strings, that means compatible collations, and it means that only comparable data types are supported i.e. spatial data types would not be, as they are not “comparable”.

2021-04-29