Performing set operations on query results with UNION, INTERSECT, and EXCEPT
The operators described in this section perform set operations on the results of two or more queries. While many of the operations
can also be performed using operations in the WHERE clause or HAVING clause, there are some operations that are very difficult
to perform in any way other than using these set-based operators. For example:
- When data is not normalized, you may want to assemble seemingly disparate information into a single result set, even though
the tables are unrelated.
- NULL is treated differently by set operators than in the WHERE clause or HAVING clause. In the WHERE clause or HAVING clause,
two null-containing rows with identical non-null entries are not seen as identical, as the two NULL values are not defined
to be identical. The set operators see two such rows as the same.
Combining sets with the UNION statement
Using EXCEPT and INTERSECT
Rules for set operations
Set operators and NULL