Offensive Security Researcher & Quantum Cryptography Analyst

How to Use UNION in PostgreSQL

Abdul Wahab Junaid
How to Use UNION in PostgreSQL

A while back, I was building a reporting feature that needed to show “recent activity” pulled from three completely different tables — orders, support tickets, and account changes — all merged into one unified timeline. My first instinct was to run three separate queries and merge the results in application code. Then I remembered UNION exists, and I moved that entire merge into a single SQL statement. It was faster, cleaner, and it’s a technique I now use constantly.

This article covers everything about UNION in PostgreSQL: how it works, the difference between UNION and UNION ALL, ordering combined results, and where this operator tends to trip people up.

What Does UNION Do?

UNION combines the result sets of two or more SELECT statements into a single result set, stacking the rows from each query on top of each other.

SELECT column1, column2 FROM table_a
UNION
SELECT column1, column2 FROM table_b;

There are two hard requirements for this to work:

  1. Each SELECT statement must return the same number of columns.
  2. The corresponding columns must have compatible data types.

The column names in the final result come from the first SELECT statement — the second and subsequent statements just need matching positions and types, not matching names.

Setting Up an Example

CREATE TABLE online_orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    amount NUMERIC(10,2),
    order_date DATE
);

CREATE TABLE store_orders (
    id SERIAL PRIMARY KEY,
    buyer_name VARCHAR(100),
    total NUMERIC(10,2),
    purchase_date DATE
);

Even though the column names differ between these two tables, I can still combine them because the data types line up:

SELECT customer_name AS name, amount, order_date AS date
FROM online_orders
UNION
SELECT buyer_name AS name, total, purchase_date AS date
FROM store_orders;

This gives me a single combined list of every purchase, whether it came from the online store or a physical location.

UNION vs UNION ALL

This is the single most important distinction to understand.

UNION removes duplicate rows from the combined result — it implicitly runs a DISTINCT operation across the merged output.

UNION ALL keeps every row, including duplicates, and doesn’t do any deduplication work.

-- Removes duplicate rows
SELECT customer_name FROM online_orders
UNION
SELECT buyer_name FROM store_orders;

-- Keeps all rows, including duplicates
SELECT customer_name FROM online_orders
UNION ALL
SELECT buyer_name FROM store_orders;

Here’s the practical difference that matters a lot: UNION has to compare every row against every other row to eliminate duplicates, which means it’s noticeably slower on large datasets. UNION ALL just concatenates the results with no extra work.

My rule of thumb: if I know the two result sets can’t logically overlap (like combining orders from two different years, or logs from two different services), I always use UNION ALL — there’s no reason to pay the performance cost of deduplication when duplicates aren’t even possible.

Ordering the Combined Results

ORDER BY can only be applied once, at the very end of the entire UNION statement — not inside each individual SELECT.

SELECT customer_name AS name, amount, order_date AS date
FROM online_orders
UNION ALL
SELECT buyer_name AS name, total, purchase_date AS date
FROM store_orders
ORDER BY date DESC;

The ORDER BY clause refers to the column names (or positions) from the final combined result — it uses the aliases defined in the first SELECT.

Combining More Than Two Queries

You can chain as many UNION (or UNION ALL) operations as needed:

SELECT product_name FROM warehouse_a
UNION ALL
SELECT product_name FROM warehouse_b
UNION ALL
SELECT product_name FROM warehouse_c;

This stacks results from three separate tables into one list.

UNION With WHERE Clauses

Each individual SELECT in a UNION can have its own filtering logic:

SELECT customer_name, amount, order_date
FROM online_orders
WHERE amount > 100
UNION ALL
SELECT buyer_name, total, purchase_date
FROM store_orders
WHERE total > 100;

This is useful when you’re merging similar but not identical datasets, each needing its own filtering rules before combining.

Adding a Source Column

A pattern I use a lot: adding a literal string column so I know which original table each row came from after merging.

SELECT customer_name AS name, amount, order_date AS date, 'online' AS source
FROM online_orders
UNION ALL
SELECT buyer_name AS name, total, purchase_date AS date, 'in_store' AS source
FROM store_orders;

This is incredibly helpful for debugging or for reports that need to break down totals by origin later, since after the merge there’s no way to tell which table a row came from unless you tag it explicitly like this.

UNION vs JOIN — Don’t Confuse Them

This confuses a lot of people early on, so let me be direct: UNION stacks rows vertically (more rows, same columns). JOIN combines rows horizontally (same number of rows or fewer, more columns). If you want to merge two similar lists into one longer list, use UNION. If you want to attach related data from one table onto rows of another, use JOIN.

INTERSECT and EXCEPT — Related Set Operators

While we’re on the topic, PostgreSQL supports two other set operators worth knowing.

INTERSECT returns only rows that appear in both result sets:

SELECT customer_name FROM online_orders
INTERSECT
SELECT buyer_name FROM store_orders;

This would show customers who bought both online and in-store (assuming the name matches exactly).

EXCEPT returns rows from the first query that do not appear in the second:

SELECT customer_name FROM online_orders
EXCEPT
SELECT buyer_name FROM store_orders;

This shows customers who bought online but never in-store. These operators follow the same column-count and type-matching rules as UNION.

A Real-World Pattern: Merging Current and Archived Data

One of the most practical applications of UNION I’ve built is combining “live” and “archived” tables. A lot of applications move old records into an archive table for performance reasons, but reporting still needs to see everything, current and historical, as one continuous dataset.

CREATE TABLE orders_current (
    id INT PRIMARY KEY,
    customer_id INT,
    amount NUMERIC(10,2),
    order_date DATE
);

CREATE TABLE orders_archive (
    id INT PRIMARY KEY,
    customer_id INT,
    amount NUMERIC(10,2),
    order_date DATE
);

CREATE VIEW all_orders AS
SELECT * FROM orders_current
UNION ALL
SELECT * FROM orders_archive;

Wrapping the UNION ALL in a view like this means every other part of the application can just query all_orders without needing to know or care that the data physically lives in two separate tables. This is a pattern I use often when a table has grown large enough to warrant partition-style splitting but the rest of the codebase shouldn’t need to be rewritten to account for it.

UNION and Column Aliases — A Common Gotcha

Beginners often assume they need matching column names across every SELECT in a UNION. You don’t — but you do need to be careful about which alias actually “wins.” Only the first SELECT statement’s column names or aliases determine the final output column names, no matter what you name things in later SELECTs.

SELECT id, amount AS total FROM orders_current
UNION ALL
SELECT id, amount AS grand_total FROM orders_archive;

The resulting column is named total, not grand_total — the second alias is simply ignored for naming purposes, even though the values from both queries are still correctly combined.

Combining UNION With INTERSECT and EXCEPT — Watch Your Parentheses

When you start mixing UNION, INTERSECT, and EXCEPT in a single statement, operator precedence becomes something you actually need to think about, because the results can differ significantly depending on how the operations are grouped.

SELECT customer_name FROM online_orders
UNION
SELECT buyer_name FROM store_orders
EXCEPT
SELECT customer_name FROM banned_customers;

Without parentheses, PostgreSQL evaluates these largely left to right, but INTERSECT actually binds more tightly than UNION and EXCEPT by default, similar to how multiplication binds tighter than addition in arithmetic. This can produce results that aren’t obvious just from reading the query top to bottom. My strong recommendation: whenever you’re combining more than one type of set operator in the same statement, use explicit parentheses to make the intended grouping unambiguous, both for PostgreSQL and for whoever reads the query next.

(
    SELECT customer_name FROM online_orders
    UNION
    SELECT buyer_name FROM store_orders
)
EXCEPT
SELECT customer_name FROM banned_customers;

This version leaves no doubt: combine the two order sources first, then remove banned customers from that combined result. I treat this kind of explicit parenthesization as mandatory in production code anytime more than one set operator type appears together — it costs nothing and prevents an entire category of subtle logic bugs.

Common Use Cases

UNION Across Tables With Slightly Different Schemas

A realistic complication I’ve run into: merging data from two tables that are conceptually similar but weren’t designed together, so their columns don’t line up perfectly. Say one system tracks a discount_percent field and another doesn’t have any concept of discounts at all.

SELECT
    customer_name AS name,
    amount,
    order_date AS date,
    discount_percent
FROM online_orders

UNION ALL

SELECT
    buyer_name AS name,
    total AS amount,
    purchase_date AS date,
    0 AS discount_percent
FROM store_orders;

Here I explicitly supply a literal 0 AS discount_percent in the second query so the column counts and types still line up correctly, even though store_orders has no real concept of discounts. This is an extremely common real-world pattern when merging data from systems that evolved independently — you don’t need identical schemas, you just need to explicitly bridge the gaps with sensible default values or NULL placeholders so both sides of the UNION produce a compatible column list.

Troubleshooting Tips

Error: “each UNION query must have the same number of columns.” Count your columns in every SELECT statement carefully — this is a strict requirement, no exceptions.

Error about incompatible types. If one query returns a VARCHAR and another returns an INT in the same column position, PostgreSQL will complain (or in some cases silently cast, which can cause confusing behavior). Explicitly cast mismatched types:

SELECT id::TEXT, name FROM table_a
UNION ALL
SELECT code::TEXT, name FROM table_b;

My UNION is slower than expected. If you don’t actually need deduplication, switch from UNION to UNION ALL. Deduplication requires sorting or hashing the entire combined result, which is expensive on large tables.

ORDER BY isn’t working as expected. Remember ORDER BY only works once, at the end of the whole statement, and refers to the final column names/aliases from the first SELECT, not any individual sub-query’s raw column names.

Duplicate rows I didn’t expect with UNION ALL. That’s expected behavior — UNION ALL never deduplicates. If you need unique rows, switch to plain UNION.

Best Practices I Follow

  1. Default to UNION ALL unless you specifically need deduplication — it’s faster and avoids unnecessary planner work.
  2. Add a source/tag column when merging from multiple tables, so you can trace origin later.
  3. Cast mismatched types explicitly rather than relying on implicit casting.
  4. Put ORDER BY only at the very end of the full combined statement.
  5. Use parentheses around individual SELECT statements when mixing UNION, INTERSECT, and EXCEPT in the same query, since operator precedence can otherwise produce unexpected results.
  6. Check EXPLAIN ANALYZE if a UNION query feels slower than expected — it’ll show you whether deduplication is the bottleneck.

Testing UNION Queries Safely Before Running Them on Production Data

Before running a UNION query against large production tables for the first time, I usually validate the column alignment on a small scale first, since a type mismatch or column-count error on a genuinely massive combined query can waste significant time before you even see the error message.

SELECT customer_name AS name, amount, order_date AS date
FROM online_orders LIMIT 5

UNION ALL

SELECT buyer_name AS name, total, purchase_date AS date
FROM store_orders LIMIT 5;

Running with LIMIT on each individual query first lets me confirm the column types, aliases, and general shape of the combined result look correct on a handful of rows, before removing the limits and running it against the full dataset. It’s a small habit, but it’s saved me from discovering a type mismatch or an unexpected NULL-handling issue only after a slow query against millions of rows had already been running for a while.

Frequently Asked Questions

Does UNION require the column names to match? No. Only the number of columns and their data type compatibility matter. The final result set uses the column names/aliases from the first SELECT.

Is UNION ALL always faster than UNION? Yes, because UNION has to perform deduplication (effectively a DISTINCT across the combined rows), while UNION ALL just concatenates results with no extra processing.

Can I use GROUP BY with UNION? Yes — each individual SELECT can have its own GROUP BY, and you can also wrap the entire UNION result in an outer query with its own GROUP BY if needed.

Can I LIMIT a UNION query? Yes, apply LIMIT at the very end, after ORDER BY, and it applies to the full combined result:

SELECT * FROM table_a
UNION ALL
SELECT * FROM table_b
ORDER BY created_at DESC
LIMIT 10;

What happens if the column types don’t match at all (like TEXT and DATE)? PostgreSQL will raise an error if there’s no reasonable implicit cast path between the types. You’ll need to explicitly cast one side to match the other.

Can I use UNION with tables that have different column counts if I don’t need all the columns? Yes, just select only the columns you actually need from each table so the counts match — you don’t need to use every column, only the same number and compatible types across each SELECT in the union.

Does UNION ALL preserve the original order rows appeared in each source query? Not reliably on its own — without an explicit ORDER BY at the end, PostgreSQL doesn’t guarantee any particular row order, even though UNION ALL does typically process rows in a fairly predictable sequence in practice. Always add ORDER BY if the sequence genuinely matters to your application.

Wrapping Up

UNION is a deceptively simple operator that solves a very common real-world problem: merging similar data scattered across multiple tables into one clean result set. Once you internalize the difference between UNION and UNION ALL, and get comfortable combining filtering, ordering, and source tagging into your combined queries, you’ll find yourself reaching for it any time you’re building activity feeds, cross-table reports, or dataset comparisons. It’s a small piece of SQL syntax that pulls a surprising amount of weight in real-world applications.

Exit mobile version