SQL UNION vs UNION ALL: Duplicates, Types and Ordering

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

Both branches must return the same number of columns with compatible types in corresponding positions. Column names need not match, because the mapping is positional; the combined output names normally come from the first query. Give the first branch useful aliases. A mismatched numeric and text position may cause an error or an implicit conversion whose behaviour depends on the engine.

UNION performs duplicate elimination across the complete selected row, including repeats within either branch. UNION ALL retains those repeats. If one list contains Leeds twice and another contains Porto once, UNION returns Leeds and Porto while UNION ALL retains both Leeds observations. Adding a source label can make two otherwise equal rows distinct, because that label becomes part of the selected combination.

NULL values are treated as equal for set-operation duplicate elimination, unlike ordinary equality comparison. Selecting unknown city twice with UNION produces one unknown result row. This is a result-set operation rather than a claim that NULL = NULL is true in a WHERE predicate. Text collation and type resolution also influence what is considered the same selected value.

Apply the final ORDER BY to the combined result, usually through an output column name or an outer query. A branch’s apparent order is not a promise about final order. Branch-local limits and ordering require dialect-specific parenthesisation or derived tables. UNION can incur a deduplication cost, while UNION ALL avoids that work when all observations should be retained. Choose according to meaning rather than automatically using the supposedly faster form; dropping legitimate repeated transactions changes totals.

UNION and UNION ALL are supported by all four systems. Type coercion, collations and branch-order syntax differ, so keep compatible expressions and use an outer query for complicated ordering. SQL Server set operations may resolve types according to its type-precedence rules, while SQLite compound SELECT has its own comparison and affinity rules.

Check the engine-specific syntax and version requirements in PostgreSQL UNION operations.

Combine the same known city list with an extra Leeds using UNION ALL and count how many occurrences the combined input contains.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT COUNT(*) AS city_occurrences FROM (
 SELECT city FROM customers WHERE city IS NOT NULL
 UNION ALL SELECT 'Leeds' AS city
) AS combined;

city_occurrences
4

The customer branch contributes three rows: two Leeds and one Porto. The literal branch contributes a fourth Leeds occurrence. UNION ALL preserves all four, and the outer count measures occurrences rather than the two distinct city labels.

Add a source-label column to both city branches and observe how the full-row comparison changes duplicate removal. Leeds from customers and Leeds from a literal branch can now remain separate because their labels differ. Remove the source label and switch between UNION and UNION ALL, counting two unique labels versus four city occurrences in the supplied example.