Union queries use keywords such as UNION, EXCEPT (MINUS in Oracle), and INTERSECT to “combine” results from two or more queries in useful ways.
UNION and UNION ALL
Use the UNION keyword to combine results from two SELECT statements into one result set. (Think of stacking the rows from two result sets.) Any duplicate rows are eliminated from the final results, unless you specify UNION ALL to preserve them.
The UNION operator conforms closely to SQL’s origin in set theory. It is used to combine two rowsets and remove any duplicates from the results. For example:
SELECT u.id, u.name FROM upfall u WHERE open_to_public = 'y' UNION SELECT u.id, u.name FROM upfall u JOIN owner o ON u.owner_id = o.id WHERE o.type = 'public';
This query lists waterfalls that are either open to the public or that are owned by a public entity (such as a national park). Duplicate elimination ensures that even if a waterfall fits into both categories (is both open to the public and owned by a public entity), it is returned only once in the query’s result set.
Duplicate elimination requires overhead, generally in the form of a limited sort operation. If you don’t need duplicate elimination, you’ll get better performance with UNION ALL.
UNION ALL is UNION without the duplicate elimination. The following UNION ALL query simulates an outer join, with
upfall as the required table and
owner as the optional table. The first SELECT picks up waterfalls that can join to
owner, whereas the second ...