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:
1 2 3 4 5 |
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.