VMware ESXi and vSphere Cluster Management

SQL UNION Operator

Learn how SQL UNION combines compatible SELECT results, removes duplicates, sorts combined rows, and differs from UNION ALL and JOIN.

The SQL UNION operator combines the result sets returned by two or more SELECT statements into one result set. It appends rows vertically: the first query contributes rows, then the next query contributes more rows.

A result set is the collection of rows and columns returned by a query. Unlike a JOIN, which combines related columns from tables on the same row, UNION combines compatible rows from separate queries.

How UNION works

Each SELECT in a union is an independent query. The queries are then combined into one output.

SELECT column_name
FROM table_1
UNION
SELECT column_name
FROM table_2;

Two or more SELECT statements can be chained with UNION.

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
UNION
SELECT city FROM Warehouses;

This pattern is useful when separate tables have similar kinds of information and you want one list of their values.

Column compatibility requirements

Every SELECT participating in a UNION must return compatible columns:

  • Every query must return the same number of columns.
  • Corresponding columns must have compatible data types.
  • Columns match by ordinal position, meaning first column with first column, second with second, and so on.
  • Output column names normally come from the first SELECT.

Column names do not need to be identical. Their positions and data types are what matter.

-- Valid: both queries return one compatible column
SELECT name FROM Customers
UNION
SELECT name FROM Suppliers;

The following query is invalid because the first query returns two columns while the second returns one:

-- Invalid: unequal column counts
SELECT name, city FROM Customers
UNION
SELECT name FROM Suppliers;

Correct it by returning the same number of expressions:

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

If corresponding positions contain incompatible types, select compatible expressions or convert one expression with the casting syntax supported by your database.

Example data: Customers and Suppliers

Assume these independent tables:

  • Customers: id, name, address, city, state_or_country, and postal_code.
  • Suppliers: id, name, address, city, state_or_country, and postal_code.

For example, customer rows might contain Paris, Paris, and Berlin, while supplier rows might contain Paris and Madrid. The city columns are compatible because both queries return one city value.

Combine customer and supplier cities

To produce one list of cities represented by either customers or suppliers, select the city column from both tables:

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers;

Because this uses UNION, repeated city values are removed. If Paris occurs several times in either table, the combined one-column result contains Paris only once.

Sort the complete combined result

Put the final ORDER BY after the last SELECT:

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

The ORDER BY clause sorts the complete combined result, not just the supplier rows. ORDER BY is the clause used to sort a final result set.

You can usually refer to the output column by name or by its ordinal position:

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

Using the output name, such as ORDER BY city, is generally clearer.

UNION and duplicate handling

UNION removes duplicate rows. A duplicate row is a row whose complete selected values match another row in the combined result.

With one selected column, duplicate checking compares that one value. With multiple selected columns, the entire combination must match.

-- Repeated cities are removed
SELECT city FROM Customers
UNION
SELECT city FROM Suppliers;

Use UNION ALL when every occurrence should remain:

-- Repeated cities are preserved
SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers
ORDER BY city;

UNION ALL combines compatible result sets without removing duplicate rows. It can be preferable when duplicates are meaningful, such as when each row represents an individual occurrence, and it often avoids the work required to detect and remove duplicates.

UNION versus UNION ALL

  • UNION: appends rows and removes duplicate complete rows.
  • UNION ALL: appends rows and preserves duplicate complete rows.

For the same city queries, UNION might return Berlin, Madrid, and Paris once each. UNION ALL returns every customer and supplier city occurrence, so Paris can appear multiple times.

Combine multiple columns

A union is not limited to one column. Each query can return several compatible columns in the same order.

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

Here, the first column from each query is the name, and the second column from each query is the city. The values are matched by position, not by source column name.

Use aliases for clear output names

A column alias is a temporary output name assigned to a column or expression. Give the first query clear aliases so the combined result has useful headings:

SELECT name AS contact_name,
       city AS contact_city
FROM Customers
UNION ALL
SELECT name AS contact_name,
       city AS contact_city
FROM Suppliers
ORDER BY contact_name;

The aliases in the second query do not normally rename the final output. The output names come from the first query, so defining them there is important.

Add a source label

A string literal is a fixed text value written in quotes. It can identify which table supplied each row:

SELECT name AS contact_name,
       city AS contact_city,
       'Customer' AS source_type
FROM Customers
UNION ALL
SELECT name AS contact_name,
       city AS contact_city,
       'Supplier' AS source_type
FROM Suppliers
ORDER BY contact_name;

Both queries return three columns in the same positions: a name, a city, and a source label. The labels make the origin of each row visible.

Filter each source before combining

A WHERE clause can be applied independently inside each component query. Each source is filtered first, and the filtered results are then combined.

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

This query returns cities from customers in CA together with cities from suppliers in the USA, removes duplicate city values, and sorts the complete result alphabetically.

Where ORDER BY belongs

In a normal union expression, place one final ORDER BY after the last SELECT:

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

An ORDER BY inside an individual component query generally does not control the order of the final union. It is normally unnecessary and may be rejected by the database unless that query is intentionally limited or wrapped in a subquery or derived table.

UNION compared with JOIN

A JOIN combines related rows from tables through a join condition, often using matching keys. It usually adds columns to a row.

-- JOIN relates rows and returns columns from both tables
SELECT Customers.name, Orders.order_date
FROM Customers
JOIN Orders ON Orders.customer_id = Customers.id;

UNION instead appends rows that have compatible shapes:

-- UNION appends rows from independent queries
SELECT name, city FROM Customers
UNION
SELECT name, city FROM Suppliers;
  • Use UNION when you need one list made from separate result sets.
  • Use JOIN when you need related information from multiple tables on the same output row.
  • Use independent WHERE clauses when each source needs different filtering before combination.

Troubleshooting UNION queries

Duplicates are missing

Cause: UNION removes duplicate complete rows.

Fix: Use UNION ALL when every source row must be returned, including repeated values.

The queries have different numbers of columns

Cause: The participating SELECT statements return unequal column counts.

Fix: Make every query return the same number of expressions in corresponding positions. If a source lacks a value, use a suitable constant or null expression supported by your database.

Corresponding data types are incompatible

Cause: Values in the same column position cannot be combined as the current data types.

Fix: Select compatible columns or explicitly convert one expression with the database's casting syntax, such as CAST.

ORDER BY does not sort the full output

Cause: The clause was placed inside an individual query or refers to an output name that is unavailable.

Fix: Put one final ORDER BY after the last SELECT. Refer to the combined output column name from the first query or use a supported ordinal position.

The output heading is unexpected

Cause: Combined output names are typically inherited from the first SELECT.

Fix: Assign the desired alias in the first query.

UNION does not show related columns together

Cause: UNION appends rows; it does not match related records.

Fix: Use an appropriate JOIN with a key or other matching condition.

Exam-relevant summary

  • UNION combines two or more compatible SELECT result sets vertically.
  • All participating queries must return the same number of columns.
  • Corresponding columns must have compatible data types.
  • Columns are matched by ordinal position, not by name.
  • Final column names normally come from the first SELECT.
  • UNION removes duplicate complete rows.
  • UNION ALL preserves duplicate complete rows.
  • Put the final ORDER BY after the last query in the union.
  • Use JOIN to relate rows and add columns; use UNION to append compatible rows.