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.

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.

Tip

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 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 ...

Get SQL Pocket Guide, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.