SQL UNION and UNION ALL: Combine Compatible Result Sets

UNION combines rows from separate queries into one result. Picture two same-shaped stacks of forms placed into one tray: each position must describe the same kind of value. UNION removes identical forms, while UNION ALL keeps every one.

A join does a different job by placing related columns beside each other. Use explicit column lists, choose duplicate handling deliberately, and do not use a growing union to hide a schema problem.

The Basic Shape

Each query in the union must return compatible columns:

SELECT email, created_at, 'customer' AS source
FROM customers

UNION

SELECT email, created_at, 'subscriber' AS source
FROM newsletter_subscribers;

The result has one column set: email, created_at, and source. PostgreSQL's docs put the compatibility rule plainly: the two queries have to return the same number of columns, and the column types need to be compatible. MySQL follows the same practical shape for everyday use.

Make the columns explicit because SELECT * is lazy in a normal query and worse in a union, where one extra column can break the whole thing.

UNION Removes Duplicates

UNION removes duplicate rows from the final result:

SELECT email
FROM customers

UNION

SELECT email
FROM newsletter_subscribers;

Suppose customers contains Alex and Sam, while newsletter_subscribers contains Sam and Taylor. The expected result has three rows. This order is shown for readability and is not guaranteed without ORDER BY:

email
------------------
alex@example.com
sam@example.com
taylor@example.com

Sam's identical row appears once. Duplicate removal changes the meaning of the result and requires extra work, so use it only when deduplication belongs to the requirement.

UNION ALL Keeps Everything

UNION ALL keeps every row from every branch:

SELECT email
FROM customers

UNION ALL

SELECT email
FROM newsletter_subscribers;

With the same sample inputs, the expected result keeps both Sam rows:

email
------------------
alex@example.com
sam@example.com
sam@example.com
taylor@example.com

Use UNION ALL when duplicates are meaningful or the inputs cannot overlap. Use UNION when the combined tray must contain distinct selected rows.

Order the Final Result

An ORDER BY at the end sorts the combined result:

SELECT title, published_at
FROM articles

UNION ALL

SELECT title, published_at
FROM archived_articles

ORDER BY published_at DESC
LIMIT 20;

That ORDER BY belongs to the final result, not only to the second query. If each input needs its own limit or sort before combining, put that query in a subquery and make the intent visible:

SELECT id, title, published_at
FROM (
    SELECT id, title, published_at
    FROM articles
    ORDER BY published_at DESC, id DESC
    LIMIT 20
) AS recent_live

UNION ALL

SELECT id, title, published_at
FROM (
    SELECT id, title, published_at
    FROM archived_articles
    ORDER BY published_at DESC, id DESC
    LIMIT 20
) AS recent_archive

ORDER BY published_at DESC, id DESC
LIMIT 20;

That is more typing, but it makes each branch's trimming rule visible before the final merge.

Use a Union for Same-shaped Results

Good union use cases usually sound like this:

• Current rows plus archived rows.
• Customers plus newsletter-only contacts.
• Internal events plus external events.
• Search hits from two same-shaped sources.
• Reports that need one list from several tables.

Weak union use cases often sound like schema confusion:

• Two tables that should be one table with a type column.
• Repeated tables by month because partitioning felt too formal.
• A report that glues unrelated facts together because the UI wants one grid.

A union can keep a legacy report running, but repeated branches become difficult to own once the report turns into business logic. When every new record type needs another branch, inspect the schema and reporting model before extending the query.

UNION Is Not a Join

Use a join when one row needs data from related tables:

SELECT orders.id, users.email
FROM orders
JOIN users ON users.id = orders.user_id;

Use a union when two queries produce the same column shape and the rows belong in one tray:

SELECT title, published_at
FROM articles

UNION ALL

SELECT title, published_at
FROM archived_articles;

If you are trying to put user columns next to order columns, read SQL joins explained. If you are stacking rows from separate sources, a union may be the right tool.

PHP and Prepared Statements

The same prepared-statement rule applies, and values belong in parameters:

<?php

$statement = $pdo->prepare(
    "SELECT title, published_at, 'live' AS source
     FROM articles
     WHERE title LIKE :term_live

     UNION ALL

     SELECT title, published_at, 'archive' AS source
     FROM archived_articles
     WHERE title LIKE :term_archive

     ORDER BY published_at DESC
     LIMIT 20"
);

$statement->execute([
    "term_live" => "%" . $query . "%",
    "term_archive" => "%" . $query . "%",
]);

Do not parameterize table names or other SQL identifiers. The PDO::prepare() documentation notes that parameter markers represent complete data literals, not identifiers or arbitrary SQL fragments. If users can choose which tables enter the union, map their choice to a fixed allowlist in PHP before building the query.

Common Pitfalls & Debugging

The Branches Return Different Column Counts

Symptom: the database rejects the union before returning rows. Cause: one branch added or removed a selected column. Fix: list every column explicitly and align each position by meaning, not only by type.

Compatible Types Change the Value

Symptom: numbers become text or dates lose the expected representation. Cause: the engine found a common output type across mismatched branch expressions. Fix: cast each branch deliberately and confirm the combined column type in the target database.

A Branch Sort Is Mistaken for Final Order

Symptom: the displayed rows move between executions. Cause: a sort inside one branch was assumed to order the combined result. Fix: apply ORDER BY after the final branch, using the output column name or supported position.

Frequently Asked Questions

Can UNION combine more than two SELECT queries?

Yes. Add another UNION or UNION ALL operator between each compatible SELECT query. Every branch must return the same number of columns in a compatible order. Use parentheses when a branch needs its own limit or ordering.

Which SELECT defines the output column names?

The first SELECT normally supplies the column names for the combined result. Alias its expressions clearly because later aliases do not rename the final columns. Refer to those output names when applying the final ORDER BY.

Can UNION combine results from the same table?

Yes. The branches may read the same table with different filters, calculations, or labels. A single SELECT with OR, CASE, or a simpler predicate may be easier, so compare the shapes before keeping separate branches.

When is UNION ALL faster than UNION?

UNION ALL can be faster when duplicate removal is unnecessary because the database can append the branch results without a distinct step. The actual difference depends on row counts and the execution plan, so choose semantics first and measure important queries.

Can you use UNION inside an INSERT statement?

Yes. INSERT ... SELECT accepts a union as its source, so several selects can load one table in a single statement. The column count and types still have to line up, exactly as they do in a standalone union.

Can a UNION combine tables from two different databases?

Not within one plain query. Crossing databases needs a feature built for it, such as a federated or foreign table, a linked server, or attaching a second file in SQLite. Otherwise fetch each result separately and combine them in application code.

Can you UNION rows that have no FROM clause?

Yes, and it is a handy way to build a small reference list inline: select a literal value, union another, and treat the result as a table. It is useful for seeding lookup data or supplying a fixed set of options to a join.

The Practical Rule

Reach for UNION ALL when you want to stack compatible rows and keep them all. Reach for UNION when duplicate removal is part of the requirement. Reach for a join when the row needs related data from another table.

And when a union keeps growing new branches, step back to schema design basics before the query becomes the schema nobody meant to design.