MySQL online course

Combine Multiple SELECT Statements with UNION in MySQL

Learn how MySQL UNION and UNION ALL combine rows from multiple SELECT statements, handle duplicates, align columns, add labels, sort results, and limit output.

UNION lets you combine the result sets from two or more SELECT statements into one result set. It appends rows vertically: the rows from one query appear beneath the rows from another.

This differs from a JOIN. A JOIN combines columns from related rows and places information side by side. Use UNION when you want one list made from separate queries, such as a list of names stored in two unrelated tables. Use JOIN when you want to show a buyer together with related order information.

This lesson assumes you know basic SELECT syntax, tables, rows, columns, aliases, and common data types. See Query A Database and Aliases for those foundations.

What UNION Does

A query returns a result set: the rows and columns produced by that query. UNION combines the rows from multiple result sets into one output.

SELECT username FROM buyers
UNION
SELECT name FROM people;

The first query supplies rows from buyers. The second supplies rows from people. The final result is one column containing values from both queries.

The source columns do not need to have the same name. In the example, username and name are different names, but both represent text values and can occupy the same output column.

UNION versus JOIN

If you want buyer usernames beneath names from another table, use UNION. If you want buyer details and matching order fields on the same row, use a JOIN.

Basic UNION Syntax

The general structure is:

SELECT expression_1, expression_2
FROM table_a
UNION
SELECT expression_1, expression_2
FROM table_b;

Each SELECT is called a branch of the union. You can chain more branches by adding another UNION and SELECT:

SELECT name FROM customers
UNION
SELECT name FROM suppliers
UNION
SELECT name FROM partners;

By default, UNION removes duplicate complete rows. The duplicate behavior is important when the same value occurs in more than one source.

Rules for UNION-Compatible SELECT Statements

Column-count requirement

Every SELECT in a UNION must return the same number of columns. This is a structural requirement, not merely a recommendation.

This valid query selects one text column from each table:

SELECT username FROM buyers
UNION
SELECT name FROM people;

This query is invalid because the first branch returns one column and the second returns two:

SELECT username FROM buyers
UNION
SELECT name, email FROM people;

MySQL reports an error indicating that the SELECT statements have different numbers of columns. Correct it by selecting only one column from the second branch:

SELECT username FROM buyers
UNION
SELECT name FROM people;

Alternatively, add a suitable expression to the branch with fewer columns:

SELECT username, email FROM buyers
UNION
SELECT name, NULL FROM people;

Here both branches return two columns. The NULL placeholder means that the people branch has no email value in this output. If the intended result requires related fields to appear side by side rather than placeholder values, reconsider whether a JOIN is the better operation.

Column positions and compatible types

UNION matches selected expressions by position, not by column name. These two branches are compatible because both first expressions are names and both second expressions are source labels:

SELECT username AS person_name, 'buyer' AS source_type
FROM buyers
UNION ALL
SELECT name, 'person' 
FROM people;

The aliases and source column names can differ. What matters is that equivalent values are placed in the same ordinal position.

Corresponding values should also have compatible data types. Character columns generally belong together, as do numeric columns. When types do not align cleanly, explicitly convert an expression so the intended output is clear:

SELECT CAST(account_number AS CHAR) AS identifier
FROM customers
UNION ALL
SELECT external_id
FROM suppliers;

Use explicit conversion carefully and choose a common representation that suits the application. See Data Types and MySQL Functions for related type and expression concepts.

Result Column Names and Aliases

The final result normally takes its column headings from the first SELECT. An alias is a temporary output name assigned to a column or expression, usually with AS.

SELECT username AS person_name
FROM buyers
UNION
SELECT name
FROM people;

The output heading is person_name. An alias in a later SELECT does not replace that heading:

SELECT username AS person_name
FROM buyers
UNION
SELECT name AS other_name
FROM people;

The final heading is still normally person_name. Put the desired alias in the first branch, where it controls the combined output label.

UNION and Duplicate Rows

UNION performs duplicate elimination. It removes duplicate complete rows from the combined result.

Suppose buyers contains Mark and people also contains Mark:

SELECT username AS person_name FROM buyers
UNION
SELECT name FROM people;

The combined output contains Mark once if the selected output row is otherwise identical. The result may therefore contain fewer rows than the total number returned by the two individual SELECT statements.

Duplicate comparison applies to the complete output row, not just one source table. For example, these rows are different because their second columns differ:

SELECT 'Mark' AS person_name, 'buyer' AS source_type
UNION
SELECT 'Mark', 'person';

Although both rows have the same name, they are not identical complete rows.

UNION ALL: Preserve Duplicates

UNION ALL appends every row and does not remove duplicates.

SELECT username AS person_name FROM buyers
UNION ALL
SELECT name FROM people;

If both tables contain Mark, the output contains both occurrences. Use UNION ALL when repeated rows are meaningful or required. It is also a good choice when you already know that duplicates cannot occur or when duplicate-elimination work is unnecessary.

Ordering and Limiting a Combined Result

To sort the entire unioned result, place ORDER BY after the final SELECT. You can use the output alias or the output column position.

SELECT username AS person_name
FROM buyers
UNION
SELECT name
FROM people
ORDER BY person_name;

This sorts the complete combined list alphabetically, rather than sorting only one branch.

You can also order by the first output column's position:

SELECT username AS person_name
FROM buyers
UNION ALL
SELECT name
FROM people
ORDER BY 1;

Using the alias is usually clearer than using a numeric position.

Put LIMIT at the end when you want to restrict the number of rows in the final combined result:

SELECT username AS person_name
FROM buyers
UNION ALL
SELECT name
FROM people
ORDER BY person_name
LIMIT 10;

This returns at most ten rows after the branches have been combined and sorted. See Sort Results and Limit Clause for more on these clauses.

Ordering an individual branch

An ORDER BY intended for the complete union belongs after the final branch. If an individual SELECT must be ordered or limited before it is unioned, use parentheses around that query:

(SELECT username AS person_name
 FROM buyers
 ORDER BY username
 LIMIT 5)
UNION ALL
SELECT name
FROM people;

Without a deliberate per-branch structure, an ORDER BY placed before the next UNION can be attached incorrectly or fail to express the intended operation. Also remember that an individual branch's ordering does not determine the order of the final combined result; apply a final ORDER BY when final output order matters.

Combining Literal Values and Expressions

A SELECT branch can return table columns, literal constants, or calculated expressions. Every branch must still return the same number of expressions.

A useful pattern is to add a source label so each output row identifies its origin:

SELECT username AS person_name, 'buyer' AS source_type
FROM buyers
UNION ALL
SELECT name, 'test record'
FROM people;

Both branches return two columns:

• The first output column contains a name.
• The second output column contains a literal source label.

The first branch provides the final headings, so the output headings are person_name and source_type.

You can also combine calculated expressions, provided the positions and types remain compatible:

SELECT CONCAT(first_name, ' ', last_name) AS display_name
FROM customers
UNION ALL
SELECT name
FROM suppliers;

Here the first branch calculates a name while the second selects a stored name. Both produce one character value.

Combining Multiple Fields

To combine customer and supplier records while preserving their origins, select the same fields in the same order from both tables:

SELECT customer_name AS person_name, 'customer' AS source_type
FROM customers
UNION ALL
SELECT supplier_name, 'supplier'
FROM suppliers
ORDER BY person_name;

This produces one two-column result. The source label is especially useful when identical names appear in both tables.

Troubleshooting UNION Queries

Different numbers of columns

Symptom: MySQL reports that the SELECT statements have different column counts.

Cause: Each UNION branch must produce the same number of selected expressions.

Fix: Remove an extra column, add a matching expression such as NULL, or redesign the query if the required data belongs side by side in a JOIN.

Expected duplicate names are missing

Symptom: A name found in both source queries appears only once.

Cause: UNION removes identical complete output rows.

Fix: Replace UNION with UNION ALL when every occurrence must remain.

Unexpected output heading

Symptom: The final column heading comes from an unexpected source.

Cause: Combined result headings normally come from the first SELECT.

Fix: Assign the desired alias in the first branch:

SELECT username AS person_name FROM buyers
UNION
SELECT name FROM people;

Values appear in the wrong output column

Symptom: Values do not line up with the intended headings.

Cause: UNION matches positions, not source column names.

Fix: Reorder each SELECT so equivalent values occupy the same ordinal position.

Sorting does not apply to the whole result

Symptom: Only one part of the output seems sorted.

Cause: The ORDER BY was attached to an individual branch or placed before the final branch.

Fix: Put ORDER BY after the last SELECT when sorting the complete unioned result. Use an output alias such as person_name or a column position such as ORDER BY 1.

UNION is being used instead of JOIN

Symptom: The query appends rows, but the goal was to show related information in columns.

Cause: UNION appends result rows and does not match records through related keys.

Fix: Use an appropriate advanced SELECT statement with a JOIN when related records must appear side by side.

Exam- and Practice-Relevant Notes

• UNION combines rows; JOIN combines columns from related rows.
• Every UNION branch must return the same number of columns.
• Corresponding columns are matched by position, not by name.
• Corresponding data types should be compatible; use explicit conversion when needed.
• The first SELECT normally determines the final column names and aliases.
• UNION removes duplicate complete rows.
• UNION ALL retains duplicate complete rows.
• A final ORDER BY sorts the complete unioned result.
• A final LIMIT restricts the complete unioned result.
• For a per-branch ORDER BY or LIMIT, use parentheses around that branch when the SQL structure requires it.

Summary

Use UNION to create one result set by appending rows from separate SELECT statements. Make the branches structurally compatible: return the same number of expressions, put related values in the same positions, and use compatible data types. Put the desired output aliases in the first SELECT.

Choose UNION when duplicate complete rows should be removed and UNION ALL when every occurrence should be preserved. Add a source-label expression when the final list needs to show where each row came from. Apply ORDER BY and LIMIT after the final branch when they should affect the complete combined result.