Combine compatible result sets
UNION combines the results of multiple SELECT statements into one result set. Every branch must return the same number of columns, in corresponding positions, with compatible data types. The output column names are generally taken from the first SELECT. UNION is useful when rows with the same meaning are stored in separate sources or when several queries represent alternate paths to one combined report.
For example, an organization may keep current and archived support contacts in separate tables but need a single directory. The branches should project a common shape and make source distinctions explicit if users need them. Aligning by position is important: SQL does not align UNION columns by alias name. A query can compile while putting a value into the wrong conceptual column if the SELECT lists are ordered inconsistently.
SELECT email, 'customer' AS account_type
FROM customers
UNION ALL
SELECT email, 'vendor' AS account_type
FROM vendors
ORDER BY email, account_type;Both branches return an email and a compatible text label. The final ORDER BY sorts the combined result. Including the source label prevents otherwise identical email rows from necessarily collapsing when duplicate elimination is requested.
UNION versus UNION ALL
UNION removes duplicate result rows; UNION ALL retains them. Duplicate elimination can require additional comparison and temporary work, so UNION ALL is often more efficient when duplicates are meaningful or impossible by design. Choose based on the required result, not a blanket rule. If the task is to combine events from separate tables, two identical-looking events can still be distinct facts. Removing one changes the answer.
Conversely, UNION may be appropriate when the requirement is a set of unique projected values. Remember that duplicate comparison considers the complete selected row, not just one field. If the result includes a source label or timestamp, two rows with the same email may remain distinct because another selected column differs. State which columns define uniqueness before using the deduplicating form.
Types, ordering, and branch behavior
Use compatible data types and explicit casts when branches need a common representation. MySQL determines result column types from the expressions in the branches; mismatches can cause coercion, truncation, or surprising ordering. Avoid mixing a numeric identifier and arbitrary text in one position just because both can be converted. Use aliases in the first branch to name output columns, then refer to result columns in the final ORDER BY.
A final ORDER BY applies to the combined result; do not rely on the order in which each branch happens to return rows. If individual branches require their own LIMIT or ordering as part of a derived result, use parentheses and verify the accepted syntax for the MySQL version. For pagination of the combined result, place the limit and order at the proper outer level, and include a deterministic tie-breaker.
Set operations and MySQL versions
UNION and UNION ALL are established MySQL syntax. Other database systems support operators such as INTERSECT and EXCEPT, but syntax and availability have varied across MySQL versions. Do not copy examples from PostgreSQL, SQL Server, or a newer manual without checking the target MySQL server. Where a needed operation is not supported on the deployed version, an EXISTS predicate, join, or carefully designed subquery can express the same business question. Test duplicate and NULL behavior rather than assuming systems share identical set semantics.
When UNION is not the right tool
If two sets represent the same entity type and are queried together often, separate tables may indicate that the model needs a shared table with a type column. If the task combines related columns from the same entity in another table, use a JOIN rather than a UNION. The two operators have different goals: UNION stacks compatible rows vertically, while JOIN combines related columns horizontally. Knowing the distinction prevents accidental data duplication or incorrect report shapes. Review joins and SELECT queries.
Before shipping a UNION query, test empty branches, duplicates, incompatible types, and ordering. Use EXPLAIN to assess large combined queries and measure the cost of duplicate elimination. Keep the SELECT lists readable and consistent, and label source information when it helps consumers interpret the result.
UNION is a precise way to combine compatible query outputs. Decide whether duplicates belong, align each branch carefully, and control final ordering explicitly. Continue with aggregates, subqueries, and query plans.