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 held in an unnormalized manner, 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.
For more information on NULL and set operations, see Set operators and NULL.
For more information, see EXCEPT statement, INTERSECT statement, and UNION statement.
Combining sets with the UNION statement
Using EXCEPT and INTERSECT
Rules for set operations
Set operators and NULL