Intersect and Except page 4
EXCEPT operator excludes duplicates of rows from query result. This fact gives us another way of duplicates elimination. Let's consider available ways on the example of the following task (Painting DB is used).
Get the colors of available spray cans.
1. Using DISTINCT keyword
2. Using GROUP BY clause
3. Using EXCEPT operator
This way is based on the idea of "subtracting" a unexisting color, ex. 'Z' from existing colors:
Because v_color column does not allow NULL, the last query can be rewritten in more universal form:
SQL Server estimates the cost of all these queries as equal. It is not surprising in view of the fact that each of the queries accomplishes scanning the table and sorting, which are the most "heavy" operations in the query execution plan.