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.
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".
Hi,
i think, it may be important to know, that it ONLY works, if both Select statements use the SAME COLLATION, if not it fails – using cast helps …
mfg Klaus
Hi Klaus, yes, for EXCEPT to work, the queries must have the same number of columns, and compatible data types. The values must also be "comparable". So for strings, that means compatible collations, and it means that only comparable data types are supported. For example, spatial data types would not be, as they are not "comparable". I've updated the blog post.