SQL Pocket Guide, 3rd Edition

Union Queries

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.

Note

Some would argue that you should use UNION ALL when you know for a certainty that no duplicates are possible, thus improving performance by avoiding the sort.

UNION

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.

Note

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

UNION ALL is UNION without the duplicate elimination. The following UNION ALL query simulates ...