SQL online course

SQL UNION Operator

Learn how SQL UNION and UNION ALL combine SELECT result sets, remove or preserve duplicates, sort results, use aliases, and resolve common errors.

The SQL UNION operator combines the row sets returned by two or more SELECT statements into one result set. A result set is the rows and columns returned by a query.

UNION is useful when related information is stored in separate tables but should be displayed as one list. For example, customer cities and supplier cities can be appended into one location list.

UNION Versus JOIN

UNION combines rows vertically. Each SELECT branch contributes more rows to the same output columns.

A JOIN combines columns horizontally by matching related rows, usually with a key or another condition. Use JOIN when you need customer and order information on the same row. Use UNION when you need to append compatible customer rows and supplier rows to one list.

Basic UNION Syntax

Each SELECT statement in a UNION is a separate query branch:

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

You can append more branches by adding another UNION and SELECT:

SELECT city FROM customers
UNION
SELECT city FROM suppliers
UNION
SELECT city FROM warehouses;

The branches are combined in the order shown, but the final order of rows is not guaranteed unless you use ORDER BY.

Customers and Suppliers Location Example

Assume these two tables contain location fields:

  • Customers: id, name, address, city, state, zip
  • Suppliers: id, name, address, city, state, zip

To produce one list of cities represented in either table, select the city column from both tables:

SELECT city
FROM Customers
UNION
SELECT city
FROM Suppliers;

If both tables contain Toronto or Brickhaven, each repeated city appears once because UNION removes duplicate complete rows.

UNION and UNION ALL

UNION removes duplicate rows from the combined result. A duplicate row means that every selected value in one output row is identical to every selected value in another output row.

UNION ALL also appends compatible result sets, but preserves duplicate rows:

SELECT city
FROM Customers
UNION ALL
SELECT city
FROM Suppliers;

With UNION ALL, every occurrence of Toronto or Brickhaven remains. This can include multiple occurrences from the same table and occurrences appearing in both tables.

  • UNION: use when you need a distinct combined list.
  • UNION ALL: use when every source occurrence matters, such as when counting records or retaining transaction rows.

Deduplication applies to the complete selected row, not merely to a source table or one particular column. For example, if you select both city and state, two rows with the same city but different states are not duplicates.

Column Compatibility Requirements

Every SELECT branch in a UNION must follow compatible output rules:

  • Each branch must return the same number of columns.
  • Corresponding columns must be in compatible positions and have compatible data types.
  • Columns are matched by position, not by source column name.
  • The output column names normally come from the first SELECT branch.

The source names do not need to match. A customer name and a supplier contact name can occupy the same output position:

SELECT customer_name AS contact_name
FROM Customers
UNION
SELECT supplier_name
FROM Suppliers;

The result has one column named contact_name because the alias is defined in the first SELECT. The second branch supplies values for that same output position even though its source column has a different name.

If one branch has an extra value, add a meaningful compatible expression, such as NULL, to the other branch:

SELECT id, name, city
FROM Customers
UNION
SELECT id, name, NULL
FROM Suppliers;

Do not add placeholder columns merely to hide a design problem. Every position should represent the same kind of output value.

Sorting UNION Results

Place one ORDER BY after the final SELECT branch. It sorts the completed combined result set:

SELECT city
FROM Customers
UNION
SELECT city
FROM Suppliers
ORDER BY city;

You can sort by the final output column name or, where supported, its ordinal position:

SELECT city
FROM Customers
UNION ALL
SELECT city
FROM Suppliers
ORDER BY 1;

The following pattern is generally incorrect for a compound query because it attempts to sort an individual branch:

SELECT city FROM Customers ORDER BY city
UNION
SELECT city FROM Suppliers;

Database-specific subquery rules can allow branch-level ordering in special cases, but that is not needed for an ordinary UNION. To sort the merged list, put ORDER BY at the end and refer to the final output name, not a source-table-qualified name such as Customers.city.

Aliases and Clear Output Labels

A column alias is an output name assigned to a selected column or expression. Define the desired label in the first branch:

SELECT city AS location
FROM Customers
UNION
SELECT city
FROM Suppliers
ORDER BY location;

The second branch does not need to repeat the alias. Giving the merged column a meaningful name makes the result easier to understand, especially when the source columns have different names. See SQL aliases for more alias examples.

Filtering Each UNION Branch

A WHERE clause can filter each SELECT independently before the branches are combined:

SELECT city
FROM Customers
WHERE state = 'CA'
UNION
SELECT city
FROM Suppliers
WHERE country = 'USA'
ORDER BY city;

Here, the customer branch selects California cities, while the supplier branch selects suppliers in the USA. UNION then combines the filtered results and removes duplicate city rows. Learn more about filtering with the SQL WHERE clause.

Compatible Data Types and CAST

Corresponding positions must have compatible data types. For example, combining a numeric customer ID with a text supplier code may fail or produce database-dependent conversion behavior.

Use CAST to explicitly convert both values to a common type:

SELECT CAST(customer_id AS VARCHAR(20)) AS entity_id
FROM Customers
UNION
SELECT CAST(supplier_code AS VARCHAR(20)) AS entity_id
FROM Suppliers;

CAST is an expression that converts a value to a specified data type. Choose a common type that makes sense for the data. Converting identifiers to text is often appropriate when one source uses numbers and another uses codes.

UNION Requirements Checklist

  • Same column count: every branch must return the same number of expressions.
  • Compatible positions: the first expression in one branch corresponds to the first expression in every other branch, and so on.
  • Compatible types: use logically related types or explicitly convert them with CAST.
  • First-branch labels: output headings normally come from the first SELECT.
  • Final sorting: put ORDER BY after the last branch.
  • Duplicate choice: use UNION to remove duplicate complete rows and UNION ALL to retain them.

Common Errors and Troubleshooting

Different numbers of columns

Error: the UNION branches return different numbers of columns.

Fix: select the same number of expressions in every branch. Use a meaningful NULL or literal placeholder only when it represents a valid value in that output position.

-- Incorrect: two columns versus one
SELECT id, city FROM Customers
UNION
SELECT city FROM Suppliers;

Incompatible data types

Error: a type conversion or incompatible datatype error occurs.

Fix: select logically compatible fields or use CAST so corresponding positions share a common type.

Duplicates unexpectedly disappear

Cause: UNION removes duplicate complete rows by design. Use UNION ALL when every matching row must remain.

ORDER BY does not sort the complete list

Cause: ORDER BY was placed inside an earlier branch or referred to a source-table-qualified column. Move one ORDER BY clause after the final SELECT and use the output alias or a supported ordinal position.

Unexpected output heading

Cause: the heading normally comes from the first branch. Define the intended column alias in that first SELECT.

UNION used instead of JOIN

UNION does not match customers and suppliers by an ID or other key. It appends rows. Use a JOIN when the goal is to place related columns from matching rows side by side.

UNION, UNION ALL, and Alternatives

  • UNION: append compatible row sets and eliminate duplicate complete rows.
  • UNION ALL: append compatible row sets while preserving every row.
  • JOIN: combine columns from related rows horizontally.
  • WHERE: filter each branch before it is unioned.
  • SELECT DISTINCT: remove duplicates from one SELECT result; UNION applies distinct treatment to the combined result unless UNION ALL is used. See SQL SELECT DISTINCT.

Quick Reference

-- Unique cities, alphabetically
SELECT city AS location
FROM Customers
UNION
SELECT city
FROM Suppliers
ORDER BY location;

-- Every city occurrence, alphabetically
SELECT city AS location
FROM Customers
UNION ALL
SELECT city
FROM Suppliers
ORDER BY location;

-- More than two branches
SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
UNION
SELECT city FROM Warehouses
ORDER BY city;

For details about sorting standalone query results, see the SQL ORDER BY clause. For the SELECT fundamentals used in each UNION branch, see the SQL SELECT statement.