SQL UNION

The UNION operator combines the result sets of two or more SELECT statements into a single result set, stacking the rows from each query on top of one another rather than joining them side by side. It is the tool to reach for when you need one unified list built from data that lives in separate tables or separate queries with the same overall shape — for example, merging a list of current employees with a list of contractors, or combining this year’s orders with last year’s archived orders into a single report. By default, UNION also removes duplicate rows from the combined output, which makes it behave like SELECT DISTINCT applied across the whole stacked result.

Overview / How UNION Works

UNION belongs to a small family of SQL set operators — alongside UNION ALL, INTERSECT, and EXCEPT (called MINUS in some dialects) — that combine the results of independent SELECT statements rather than combining columns from related tables the way a JOIN does. This distinction matters: a JOIN matches rows from two tables on a condition and produces wider rows (more columns), while UNION stacks rows from same-shaped queries and produces more rows of the same width. If you find yourself wanting to add columns, you want a join; if you want to add rows from another query, you want a union.

For a UNION to be valid, every SELECT in the chain must return the same number of columns, in the same left-to-right order, with compatible data types in each position. The column names that appear in the final combined result come from the first SELECT only — aliases used in later branches are ignored for naming purposes, even though the values themselves are still included. SQLite is lenient about type mismatches across branches because of its dynamic type system, but most other databases (PostgreSQL, MySQL, SQL Server) are stricter, so it is good practice to keep types aligned regardless of which engine you target.

The deduplication that plain UNION performs is not free. Conceptually, the engine must compare every combined row against every other row to find exact duplicates, which is typically implemented by sorting the combined rows (or building a temporary hash/index structure) and then discarding adjacent identical rows — the same mechanical cost as a SELECT DISTINCT. UNION ALL skips this step entirely: it simply concatenates the result sets as-is, which is why it is always at least as fast as UNION, and often considerably faster on large result sets. A common rule of thumb is to default to UNION ALL unless you specifically need duplicates removed.

Syntax

The general form of a UNION looks like this (this is a template, not runnable SQL — the bracketed and placeholder parts are not literal syntax):

SELECT column1, column2, ...
FROM table_a
UNION [ALL]
SELECT column1, column2, ...
FROM table_b
ORDER BY column1;

Part	Meaning
First SELECT ... FROM ...	Determines the number of output columns, the column names used in the final result, and the reference data type for each column position.
UNION	Combines the two result sets and removes duplicate rows, comparing the entire row (all columns), not just one column.
UNION ALL	Combines the two result sets and keeps every row, including duplicates. No deduplication pass is performed, so it is faster.
Second (and later) SELECT ... FROM ...	Must return the same number of columns as the first query, in the same order, with compatible types.
ORDER BY (optional)	Appears only once, after the last SELECT in the chain. Sorts the final combined result and refers to column names/aliases from the first SELECT, or to column position (ORDER BY 1).

Examples

Example 1: Deduplicating rows from two tables with UNION

CREATE TABLE current_employees (name TEXT, email TEXT);
INSERT INTO current_employees VALUES
  ('Alice Chen', 'alice@example.com'),
  ('Brian Doyle', 'brian@example.com');

CREATE TABLE newsletter_subscribers (name TEXT, email TEXT);
INSERT INTO newsletter_subscribers VALUES
  ('Brian Doyle', 'brian@example.com'),
  ('Carla Nunez', 'carla@example.com');

SELECT name, email FROM current_employees
UNION
SELECT name, email FROM newsletter_subscribers
ORDER BY name;

Result:

name	email
Alice Chen	alice@example.com
Brian Doyle	brian@example.com
Carla Nunez	carla@example.com

Brian Doyle appears in both source tables with identical name and email values, so the combined row would be an exact duplicate. UNION collapses the two copies into one, leaving three distinct people in the final mailing list instead of four.

Example 2: Preserving duplicates with UNION ALL

CREATE TABLE store_north (item TEXT, amount REAL);
INSERT INTO store_north VALUES
  ('Widget', 25.00),
  ('Gadget', 40.00);

CREATE TABLE store_south (item TEXT, amount REAL);
INSERT INTO store_south VALUES
  ('Widget', 25.00),
  ('Thingamajig', 15.50);

SELECT item, amount FROM store_north
UNION ALL
SELECT item, amount FROM store_south
ORDER BY item;

Result:

item	amount
Gadget	40.00
Thingamajig	15.50
Widget	25.00
Widget	25.00

Both stores happen to have sold a Widget for the same price, producing two identical rows. Because this uses UNION ALL instead of plain UNION, both sales are kept — which is exactly what you want when combining transaction logs, since each row represents a real, separate event and collapsing them would understate the true sales count. Had this query used UNION, only one Widget row would have survived.

Example 3: Combining different sources with a discriminator column

CREATE TABLE active_orders (order_id INTEGER, customer TEXT, total REAL);
INSERT INTO active_orders VALUES
  (101, 'Nora Kim', 120.00),
  (102, 'Sam Patel', 75.50);

CREATE TABLE archived_orders (order_id INTEGER, customer TEXT, total REAL);
INSERT INTO archived_orders VALUES
  (201, 'Nora Kim', 60.00),
  (202, 'Elle Fischer', 300.00);

SELECT order_id, customer, total, 'active' AS status FROM active_orders
UNION
SELECT order_id, customer, total, 'archived' AS status FROM archived_orders
ORDER BY customer, order_id;

Result:

order_id	customer	total	status
202	Elle Fischer	300.00	archived
101	Nora Kim	120.00	active
201	Nora Kim	60.00	archived
102	Sam Patel	75.50	active

Nora Kim appears twice here, but the rows are not duplicates — the order_id, total, and status differ between her active and archived order, so UNION keeps both. The literal string 'active' and 'archived' added as a fourth column is a common technique: since active_orders and archived_orders have no column that says which table a row came from, injecting a constant label lets you tell the two sources apart after they have been stacked together.

How It Works Step by Step / Under the Hood

Each branch of a UNION is planned and executed as its own independent query, going through the normal logical processing order for that branch: FROM (and any joins) → WHERE → GROUP BY → HAVING → SELECT (including any expressions or aliases) → and, only for the very last branch, ORDER BY / LIMIT. Nothing about one branch’s filtering or grouping affects another branch — they are fully separate result sets until the union step.

Once each branch has produced its rows, the engine appends them together in the order the branches were written. For plain UNION, it then performs a deduplication pass over the combined set — comparing full rows and discarding exact repeats, conceptually equivalent to running SELECT DISTINCT over the stacked result. For UNION ALL, this pass is skipped and the concatenated rows are the final answer.

Finally, if an ORDER BY clause is present, it is applied exactly once, to the whole combined result set, after the union (and after any deduplication) has happened. This is why ORDER BY can only reference column names or aliases from the first SELECT — by the time sorting happens, the engine only knows the output column names established by that first branch. The same applies to LIMIT: it caps the final combined, sorted result, not any individual branch.

Common Mistakes

Mistake 1: Mismatched column counts

Every branch of a UNION must return the same number of columns. Mixing a two-column query with a one-column query fails:

SELECT name, department FROM employees
UNION
SELECT customer_name FROM customers;

This fails because the first SELECT returns 2 columns and the second returns only 1. The fix is to make both sides return the same number of columns:

SELECT name, department FROM employees
UNION
SELECT customer_name, country FROM customers;

Now both branches return 2 columns, so the union succeeds, combining every employee’s name/department pair with every customer’s name/country pair into one result set named name and department (the column names come from the first SELECT).

Mistake 2: Using UNION when you actually need every row

It is easy to reach for UNION out of habit and accidentally lose real data that happens to look like a duplicate:

CREATE TABLE web_signups (email TEXT);
INSERT INTO web_signups VALUES ('a@example.com'), ('b@example.com'), ('a@example.com');

CREATE TABLE app_signups (email TEXT);
INSERT INTO app_signups VALUES ('a@example.com'), ('c@example.com');

SELECT email FROM web_signups
UNION
SELECT email FROM app_signups;

This runs without error and returns only 3 rows (a@example.com, b@example.com, c@example.com), even though a@example.com actually signed up three separate times across the two tables. UNION silently collapsed those repeats. If the goal is to count total signup events rather than distinct emails, this under-counts. The fix is UNION ALL:

CREATE TABLE web_signups (email TEXT);
INSERT INTO web_signups VALUES ('a@example.com'), ('b@example.com'), ('a@example.com');

CREATE TABLE app_signups (email TEXT);
INSERT INTO app_signups VALUES ('a@example.com'), ('c@example.com');

SELECT email FROM web_signups
UNION ALL
SELECT email FROM app_signups;

This returns all 5 rows — one per actual signup event — preserving the true count.

Mistake 3: Putting ORDER BY on the wrong SELECT

Attaching ORDER BY to an individual branch instead of the end of the whole compound query is invalid:

SELECT name, department FROM employees ORDER BY name
UNION
SELECT customer_name, country FROM customers;

SQLite only allows one ORDER BY, and it must come after the last SELECT in the chain, not attached to an earlier branch — this raises a syntax error. The fix is to move it to the end:

SELECT name, department FROM employees
UNION
SELECT customer_name, country FROM customers
ORDER BY name;

This sorts the final, already-combined and already-deduplicated result by the name column.

Best Practices

• Default to UNION ALL unless you specifically need duplicates removed — it is faster and won’t silently hide legitimate repeated rows.
• List columns explicitly in every branch instead of using SELECT *, so a future schema change to one table can’t silently shift columns or break the union.
• Add a literal discriminator column (e.g. 'active' AS status) when combining rows from conceptually different sources, so downstream consumers can tell which branch a row came from.
• Keep column types aligned across branches, and use explicit CAST when they legitimately differ, rather than relying on implicit conversion.
• Put ORDER BY and LIMIT only once, at the very end of the whole compound query.
• Remember column names in the output come from the first SELECT — alias consistently there if downstream code depends on the names.

Practice Exercises

• Write a UNION query that returns a single deduplicated list of names, combining every employee’s name with every customer’s customer_name, sorted alphabetically.
• Write a query that lists, in one column, every distinct department value from the employees table together with every distinct category value from the products table, using UNION so identical text values only appear once.
• Without running it, predict the difference in row count between SELECT department FROM employees UNION SELECT department FROM employees and the same query using UNION ALL instead. Then think through why: how many distinct department values are there versus the total number of rows in the table?

Summary

• UNION combines the result sets of two or more SELECT statements into one, stacking rows vertically rather than joining columns side by side.
• Every branch must return the same number of columns, in the same order, with compatible types; output column names come from the first SELECT.
• Plain UNION removes duplicate rows by comparing entire rows; UNION ALL keeps every row, including duplicates, and is faster because it skips deduplication.
• Only one ORDER BY (and one LIMIT) is allowed, placed after the final SELECT, applying to the whole combined result.
• UNION is different from JOIN: JOIN adds columns by matching related rows; UNION adds rows from same-shaped queries.
• Add a literal column to distinguish rows when combining conceptually different sources.