UNION in PostgreSQL

The UNION operator combines the result sets of two or more SELECT queries into a single result set. By default, UNION removes duplicate rows. Use UNION ALL to retain all rows including duplicates — it is faster because no deduplication is performed.

Syntax

SELECT select_list FROM table_a
UNION
SELECT select_list FROM table_b;

To keep duplicates:

SELECT select_list FROM table_a
UNION ALL
SELECT select_list FROM table_b;

To sort the combined result, add ORDER BY after the final query:

SELECT select_list FROM table_a
UNION ALL
SELECT select_list FROM table_b
ORDER BY sort_expression;

Practical Example

Suppose you manage two event registration systems that were merged. Each still has its own table:

CREATE TABLE registrations_2024 (
  attendee_id   SERIAL PRIMARY KEY,
  full_name     VARCHAR NOT NULL,
  email         VARCHAR NOT NULL,
  event_slug    VARCHAR NOT NULL
);

CREATE TABLE registrations_2025 (
  attendee_id   SERIAL PRIMARY KEY,
  full_name     VARCHAR NOT NULL,
  email         VARCHAR NOT NULL,
  event_slug    VARCHAR NOT NULL
);

INSERT INTO registrations_2024 (full_name, email, event_slug) VALUES
  ('Alice Nguyen',   'alice@example',   'summit-2024'),
  ('Bob Okafor',     'bob@example',     'summit-2024'),
  ('Clara Hoffman',  'clara@example',   'workshop-2024');

INSERT INTO registrations_2025 (full_name, email, event_slug) VALUES
  ('Alice Nguyen',   'alice@example',   'summit-2025'),
  ('David Reyes',    'david@example',   'summit-2025'),
  ('Clara Hoffman',  'clara@example',   'workshop-2025');

List every email address across both years, removing duplicates:

SELECT email FROM registrations_2024
UNION
SELECT email FROM registrations_2025
ORDER BY email;

Result:

        email
----------------------
 alice@example
 bob@example
 clara@example
 david@example

Alice and Clara appear in both tables but UNION returns each email once.

List all registrations including duplicates across both years (for counting purposes):

SELECT full_name, email, event_slug FROM registrations_2024
UNION ALL
SELECT full_name, email, event_slug FROM registrations_2025
ORDER BY full_name, event_slug;

Result:

   full_name   |         email          |    event_slug
---------------+------------------------+----------------
 Alice Nguyen  | alice@example      | summit-2024
 Alice Nguyen  | alice@example      | summit-2025
 Bob Okafor    | bob@example        | summit-2024
 Clara Hoffman | clara@example      | workshop-2024
 Clara Hoffman | clara@example      | workshop-2025
 David Reyes   | david@example      | summit-2025

UNION ALL preserves both Alice and Clara’s registrations from each year.

Using UNION to combine aggregates

You can also UNION aggregated queries to produce a combined summary:

SELECT 'Q1 2024' AS period, COUNT(*) AS registrations FROM registrations_2024 WHERE event_slug LIKE '%-2024'
UNION ALL
SELECT 'Q1 2025' AS period, COUNT(*) AS registrations FROM registrations_2025 WHERE event_slug LIKE '%-2025'
ORDER BY period;

UNION vs UNION ALL Performance

For large result sets, UNION ALL avoids a costly sort-and-hash pass. When in doubt, check with EXPLAIN ANALYZE to see whether the deduplication step is worth the cost.

Testing with Vela

When consolidating tables from separate systems (like the dual-registration example above), use Vela’s database branching to test your UNION queries against a production-data branch before the migration goes live. You can validate deduplication logic, verify column type compatibility, and confirm ORDER BY behavior without any impact on the live database.

Production Tips

• Prefer UNION ALL over UNION when you know the component queries return non-overlapping rows. The deduplication in UNION requires a sort or hash of the full combined result set.
• Column names in the output come from the first SELECT. Use aliases in the first query to control the output column names.
• You cannot place ORDER BY, LIMIT, or FETCH inside individual component queries — only after the final query in the chain. Wrap individual queries in parentheses if you need to LIMIT before combining.
• For combining data from many tables with the same structure (e.g., partitioned monthly log tables), UNION ALL over a set of similar tables is a common pattern. Consider a partitioned table or a view that UNION ALLs the partitions for cleaner access.
• When component queries have mismatched data types for corresponding columns, use explicit CAST in the first query to establish the output type: CAST(amount AS NUMERIC(10,2)).