VMware ESXi and vSphere Cluster Management

Combine Multiple SELECT Statements in MySQL with UNION

Learn how to combine MySQL SELECT results with UNION and UNION ALL, match columns, remove or preserve duplicates, add aliases, label sources, and sort the final result.

When separate queries produce similar kinds of rows, MySQL can combine their result sets into one result. The main set operators for this task are UNION and UNION ALL.

A result set is the table-like collection of rows returned by a query. UNION combines these rows vertically: rows from one SELECT are appended below rows from another SELECT.

UNION Versus JOIN

UNION combines query output by adding rows. It does not combine columns from related tables.

A JOIN is different. A JOIN combines columns from related rows, usually by matching values such as an ID. Use UNION when you want one list made from multiple SELECT results. Use JOIN when you want information from related tables on the same row.

For example, combining names from a buyers table and a contacts table is a UNION task. Combining each buyer with that buyer's order details is usually a JOIN task.

Basic UNION Syntax

The general form is:

SELECT expression_list
FROM source1
UNION
SELECT expression_list
FROM source2;

You can append more SELECT statements by adding more UNION operators:

SELECT expression_list FROM source1
UNION
SELECT expression_list FROM source2
UNION
SELECT expression_list FROM source3;

Suppose buyers has a username column and testtb has a name column. Although the source columns have different names, both queries can return one name-related value:

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

The first SELECT supplies the output heading, so the combined result uses person_name.

Requirements for SELECT Statements in a UNION

Every SELECT in a UNION must follow a compatible output shape.

Requirement | Explanation | Correct approach | Common problem Equal number of selected columns | Every SELECT must return the same number of expressions. | Select one expression in each query, or two in each query. | One SELECT returns more columns than another. Corresponding column positions | First columns are combined together, second columns are combined together, and so on. | Put logically matching expressions in the same position. | A city is accidentally placed where a name should be. Compatible data types | MySQL must be able to sensibly combine values in each position. | Combine compatible text, numeric, date, or other values. | Unrelated types produce conversion or misleading-output issues. Output names from the first SELECT | The final headings normally come from the first SELECT. | Put the desired aliases in the first SELECT. | An alias added only to a later SELECT does not rename the final column.

Use Explicit Column Lists

Do not use SELECT * unless the tables have deliberately matching structures and column order. Different tables often have different column counts or place unrelated columns in the same position.

Explicit expressions make the intended output clear:

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

Column order matters. In a two-column UNION, MySQL combines the first expression from each SELECT into output column one, then combines the second expression from each SELECT into output column two.

UNION and Duplicate Rows

UNION removes duplicate complete rows from the combined result. A duplicate means that all output columns in one row match the corresponding output columns in another row.

For example, if both tables contain the name Jordan, the default UNION returns one row for that one-column result:

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

UNION ALL preserves every row, including repeated rows:

SELECT username AS person_name FROM buyers
UNION ALL
SELECT name FROM testtb;
Operator | Duplicate handling | Typical use case | Performance consideration UNION | Removes duplicate complete rows. | Return a distinct combined list. | MySQL generally performs duplicate-elimination work. UNION ALL | Preserves all rows. | Every source row is needed, including repeats. | Generally avoids duplicate-elimination work and can be faster.

Text duplicate comparisons can be affected by the active collation. A collation is a set of rules MySQL uses to compare and sort character strings. Many configurations use case-insensitive comparisons, so values that differ only by letter case may be treated as duplicates. Review the relevant character set and collation when case distinctions matter.

Output Column Names and Aliases

A column alias is a temporary output name assigned to a selected expression with AS, or with an equivalent alias form. In a UNION result, the column names normally come from the first SELECT.

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

Here, person_name is the meaningful heading for values from both sources. Use an alias that describes every value in that output column. An alias such as buyer_username would be misleading if the same column also contains contact names.

Combining Multiple Columns

Multiple-column UNION queries are valid when each SELECT returns the same number of expressions in the same logical order:

SELECT username AS person_name, city AS location
FROM buyers
UNION
SELECT name, surname
FROM testtb;

This example has two output positions. The first position combines username with name. The second position combines city with surname. The expressions do not need identical original column names, but their meanings and data types should be compatible. If surname is not actually a location, the query may be syntactically valid but logically misleading.

Selecting Literals and Identifying Row Sources

A UNION expression can include literal values as well as table columns. A literal is a fixed value written directly in the query. Adding a source label helps identify where each result row came from:

SELECT username AS person_name, 'buyers' AS source_table
FROM buyers
UNION ALL
SELECT name, 'testtb' AS source_table
FROM testtb;

Both SELECT statements return two columns: a person name and a source label. The first SELECT gives the final headings, person_name and source_table. The labels are consistent with the values they describe.

You can also use a literal or NULL placeholder to align column counts when a source does not have a corresponding value. Do this only when the missing value has a clear meaning:

SELECT username AS person_name, city AS location
FROM buyers
UNION ALL
SELECT name, NULL
FROM testtb;

The second SELECT still returns two expressions. However, the NULL location should represent a genuinely unknown or unavailable location, not merely hide a badly designed output shape.

Ordering the Combined Result

Without ORDER BY, SQL does not guarantee the order of rows. To sort the complete UNION result, place one ORDER BY after the final SELECT:

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

ORDER BY is the clause used to sort a result set. In this form, it applies to the complete combined result, not only to the rows from testtb. You can order by the final output column name or by the alias supplied by the first SELECT.

When using several SELECT statements, put ORDER BY after the last one:

SELECT username AS person_name FROM buyers
UNION ALL
SELECT name FROM testtb
UNION ALL
SELECT display_name FROM archived_people
ORDER BY person_name;

Common Problems and Fixes

Different Numbers of Columns

If one SELECT returns more expressions than another, MySQL cannot combine them as a UNION.

SELECT username, city FROM buyers
UNION
SELECT name FROM testtb;

Make every SELECT return the same number of expressions. Add a meaningful literal or NULL placeholder only when appropriate.

Unexpected Missing Repeated Values

If expected repeats do not appear, the likely cause is the duplicate-removal behavior of UNION. Replace UNION with UNION ALL when every returned row must remain visible.

Unexpected Output Heading

If the heading is unexpected, check the first SELECT. The final result normally takes its names from that SELECT, so place the desired alias there.

Unpredictable Row Order

Rows may appear in different orders when no ORDER BY is present. Add ORDER BY after the final SELECT to sort the complete result.

Text Values Treated as Duplicates

If values that look different are treated as duplicates, the active collation may compare text without case sensitivity. Review the collation or use UNION ALL if retaining both source rows is the actual requirement.

Problems Caused by SELECT *

SELECT * can cause a UNION error when tables have different column counts. It can also produce misleading results when matching positions contain unrelated fields. Replace it with explicit columns in a deliberate order.

UNION Checklist

  • Use UNION to append rows from multiple result sets; use JOIN to combine columns from related rows.
  • Make every SELECT return the same number of expressions.
  • Place logically corresponding values in the same column position.
  • Use compatible data types in corresponding positions.
  • Use UNION to remove duplicate complete rows.
  • Use UNION ALL to preserve duplicates and avoid unnecessary duplicate elimination.
  • Put meaningful aliases in the first SELECT.
  • Add literal source labels when row origin matters.
  • Put ORDER BY after the final SELECT when the complete result must be sorted.
  • Prefer explicit column lists over SELECT *.

For a focused reference, see combining multiple SELECT statements in MySQL.