SQL 40 🛢️ Combining Query Results with UNION and UNION ALL
The UNION operator combines the result sets of two or more SELECT statements into a single result set. Unlike a join, which combines columns from two tables side by side, a union combines rows from two queries one after another. The two queries must have the same number of columns, the corresponding columns must have compatible data types, and the column names in the result come from the first query. The union is the SQL mechanism for appending one result set to another, which makes it the tool for combining data from multiple sources, for implementing the equivalent of a full outer join in databases that do not support one, and for splitting a complex query into simpler parts.
The distinction between UNION and UNION ALL is about duplicate rows. UNION removes duplicates from the combined result, which requires the database to compare every row against every other row and is expensive. UNION ALL keeps all rows, including duplicates, and is significantly faster because it performs no comparison. The rule of thumb is to use UNION ALL by default and UNION only when duplicates must be removed.
This chapter covers the syntax of UNION and UNION ALL, the requirements for the combined queries, the difference in duplicate handling and performance, the ordering of the result, the patterns for combining data from multiple sources, and the use cases where union is the right tool.
Key point: UNION combines rows from two or more SELECT statements. The queries must have the same number of columns with compatible types. UNION removes duplicates; UNION ALL keeps them. UNION ALL is faster because it does no comparison. The column names in the result come from the first query. ORDER BY applies to the entire combined result and can only appear at the end.
Why UNION and UNION ALL exist
The multiple-source problem. Data is often spread across multiple tables with the same structure: monthly sales tables, regional customer tables, archive and active tables. A query that needs the combined data uses a union to append the rows from each source.
The full-outer-join problem. MySQL does not support FULL OUTER JOIN. The equivalent result is produced by a UNION of a LEFT JOIN and a RIGHT JOIN. This is one of the most common uses of union in practice.
The duplicate problem. Some queries produce duplicate rows that must be removed. UNION removes them as part of the operation. UNION ALL does not, and it is the correct choice when duplicates are either impossible or desired.
The performance problem. UNION must sort or hash the combined result to find and remove duplicates. On large result sets, this is expensive. UNION ALL simply concatenates the results and is much faster.
The query-decomposition problem. A complex query with multiple conditions can sometimes be expressed as a union of simpler queries, one per condition. The result is the same, and each part is easier to reason about and optimize.
a. Basic UNION syntax
The UNION operator appears between two SELECT statements.
SELECT name, email FROM customers
UNION
SELECT name, email FROM leads;The result contains all rows from both queries, with duplicates removed. If a row appears in both customers and leads, it appears once in the result.
The number of columns must match:
-- Valid: both queries have two columns
SELECT name, email FROM customers
UNION
SELECT name, email FROM leads;
-- Invalid: different number of columns
SELECT name, email FROM customers
UNION
SELECT name FROM leads; -- ErrorThe data types of corresponding columns must be compatible:
-- Valid: both first columns are strings, both second columns are numbers
SELECT name, age FROM customers
UNION
SELECT name, age FROM leads;
-- Invalid: incompatible types
SELECT name, age FROM customers
UNION
SELECT name, hire_date FROM leads; -- ErrorThe column names in the result come from the first query. The second query’s column names are ignored.
SELECT name AS customer_name, email FROM customers
UNION
SELECT full_name, email_address FROM leads;
-- Result columns: customer_name, emailb. UNION ALL
UNION ALL combines the results without removing duplicates.
SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM leads;If a row appears in both tables, it appears twice in the result. If a row appears three times across the sources, it appears three times.
UNION ALL is faster than UNION because it does not compare rows. The database simply appends the second result to the first.
| Aspect | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Kept |
| Performance | Slower (comparison) | Faster (append) |
| Use case | Deduplication required | All rows required |
| Sort | Implicit | None |
The rule of thumb is to use UNION ALL unless duplicates must be removed. Even when duplicates exist, they may be acceptable or expected, and UNION ALL avoids the cost of removing them.
c. Requirements for the combined queries
The queries combined by a union must satisfy three requirements.
Same number of columns. Each SELECT must return the same number of columns.
-- Invalid
SELECT id, name FROM a
UNION
SELECT id FROM b; -- ErrorCompatible data types. The corresponding columns must have compatible types. The database determines compatibility based on its type system. Numeric types are generally compatible with each other, and string types are compatible with each other. Mixing a number and a string may require an explicit cast.
-- Invalid: number vs string
SELECT id, age FROM a
UNION
SELECT id, name FROM b; -- Error
-- Valid: explicit cast
SELECT id, CAST(age AS VARCHAR) FROM a
UNION
SELECT id, name FROM b;Same order of columns. The columns are matched by position, not by name. The first column of the first query is combined with the first column of the second query, and so on. If the orders differ, the result is wrong.
-- Wrong: columns in different order
SELECT name, age FROM a
UNION
SELECT age, name FROM b; -- Result is wrong
-- Right: same order
SELECT name, age FROM a
UNION
SELECT name, age FROM b;The union does not match columns by name. It matches them by position. This is a common source of errors when the queries are written separately.
d. Ordering the result
ORDER BY applies to the entire combined result and can only appear at the end of the last query.
SELECT name, email FROM customers
UNION
SELECT name, email FROM leads
ORDER BY name;The ORDER BY sorts the combined result, not the individual queries. Placing it in the first query is a syntax error.
-- Invalid
SELECT name, email FROM customers ORDER BY name
UNION
SELECT name, email FROM leads; -- ErrorTo sort the result of a single query before the union, wrap it in a subquery:
SELECT * FROM (
SELECT name, email FROM customers ORDER BY name LIMIT 10
) AS top_customers
UNION ALL
SELECT name, email FROM leads;The subquery sorts and limits the first result, and the union appends the second. The final ORDER BY on the combined result is applied last.
e. Combining data from multiple sources
The union is the tool for combining rows from tables with the same structure.
SELECT 'active' AS status, id, name FROM active_users
UNION ALL
SELECT 'archived' AS status, id, name FROM archived_users;The literal 'active' and 'archived' distinguish the source of each row. This pattern is common when the source table is not part of the result but its identity matters.
A union can combine more than two queries:
SELECT id, name FROM january_sales
UNION ALL
SELECT id, name FROM february_sales
UNION ALL
SELECT id, name FROM march_sales
ORDER BY name;Each query contributes its rows, and the result is the combined set. The ORDER BY applies to the whole.
The union is also used to simulate FULL OUTER JOIN in MySQL:
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;The LEFT JOIN produces matched rows and left-only rows. The RIGHT JOIN produces matched rows and right-only rows. The UNION removes the duplicate matched rows, leaving the equivalent of a full outer join.
f. UNION vs UNION ALL performance
The performance difference between UNION and UNION ALL is the cost of duplicate removal. UNION must sort or hash the combined result to find duplicates, and the sort or hash is proportional to the size of the result.
| Rows in each query | UNION ALL | UNION |
|---|---|---|
| 1,000 + 1,000 | Append, no sort | Sort 2,000 rows |
| 100,000 + 100,000 | Append | Sort 200,000 rows |
| 1,000,000 + 1,000,000 | Append | Sort 2,000,000 rows |
For large results, the difference is significant. UNION may be orders of magnitude slower than UNION ALL because of the sort.
The choice between them should be driven by whether duplicates must be removed. If the queries are known to produce disjoint results, UNION ALL is always correct and always faster. If duplicates are possible but acceptable, UNION ALL is still correct. Only when duplicates must be removed is UNION necessary.
Complete Example Session
-- ============================================
-- PART 1: CREATE SAMPLE TABLES
-- ============================================
CREATE TABLE customers (
name VARCHAR(50),
email VARCHAR(100)
);
CREATE TABLE leads (
name VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO customers VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');
INSERT INTO leads VALUES
('Bob', 'bob@example.com'),
('Carol', 'carol@example.com');-- ============================================
-- PART 2: UNION REMOVES DUPLICATES
-- ============================================
SELECT name, email FROM customers
UNION
SELECT name, email FROM leads;
-- Alice, Bob, Carol (Bob appears once)-- ============================================
-- PART 3: UNION ALL KEEPS DUPLICATES
-- ============================================
SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM leads;
-- Alice, Bob, Bob, Carol (Bob appears twice)-- ============================================
-- PART 4: COLUMN COUNT MISMATCH
-- ============================================
-- Invalid: different number of columns
-- SELECT name FROM customers
-- UNION
-- SELECT name, email FROM leads;-- ============================================
-- PART 5: COLUMN TYPE MISMATCH
-- ============================================
-- Invalid: number vs string
-- SELECT name, age FROM customers
-- UNION
-- SELECT name, email FROM leads;-- ============================================
-- PART 6: ORDER BY AT THE END
-- ============================================
SELECT name, email FROM customers
UNION
SELECT name, email FROM leads
ORDER BY name;
-- Sorts the entire combined result-- ============================================
-- PART 7: COMBINING MULTIPLE SOURCES
-- ============================================
SELECT 'active' AS status, id, name FROM active_users
UNION ALL
SELECT 'archived' AS status, id, name FROM archived_users
ORDER BY name;-- ============================================
-- PART 8: FULL OUTER JOIN SIMULATION IN MYSQL
-- ============================================
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;-- ============================================
-- PART 9: UNION ALL WITH A LITERAL COLUMN
-- ============================================
SELECT 'customer' AS source, name FROM customers
UNION ALL
SELECT 'lead' AS source, name FROM leads;
-- Each row labeled with its source-- ============================================
-- PART 10: SORTED TOP-N FROM EACH SOURCE
-- ============================================
SELECT * FROM (
SELECT name, score FROM scores_a ORDER BY score DESC LIMIT 5
) AS top_a
UNION ALL
SELECT * FROM (
SELECT name, score FROM scores_b ORDER BY score DESC LIMIT 5
) AS top_b
ORDER BY score DESC;
-- Top 5 from each source, combined and sortedThese ten parts cover creating the tables, UNION removing duplicates, UNION ALL keeping duplicates, column count mismatch, column type mismatch, ORDER BY at the end, combining multiple sources, the full outer join simulation, UNION ALL with a literal column, and sorted top-N from each source.
Quick Reference
UNION vs UNION ALL
| Aspect | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Kept |
| Performance | Slower | Faster |
| Sort | Implicit | None |
| Use case | Deduplication | All rows |
Requirements
| Requirement | Rule |
|---|---|
| Columns | Same number |
| Types | Compatible |
| Order | Same position |
| Names | From the first query |
Ordering
| Rule | Detail |
|---|---|
| Position | Only at the end |
| Applies to | Entire combined result |
| Per-query sort | Wrap in a subquery |
Full Outer Join Simulation
| Step | Query |
|---|---|
| 1 | LEFT JOIN |
| 2 | UNION |
| 3 | RIGHT JOIN |
Performance
| Rows | UNION ALL | UNION |
|---|---|---|
| 1,000 + 1,000 | Append | Sort 2,000 |
| 100,000 + 100,000 | Append | Sort 200,000 |
| 1,000,000 + 1,000,000 | Append | Sort 2,000,000 |
Best Practices
✅ Do This:
-- Use UNION ALL by default
SELECT name FROM customers
UNION ALL
SELECT name FROM leads;
-- Use UNION only when duplicates must be removed
SELECT name FROM customers
UNION
SELECT name FROM leads;
-- Put ORDER BY at the end
SELECT name FROM a
UNION ALL
SELECT name FROM b
ORDER BY name;
-- Add a literal column to distinguish sources
SELECT 'customer' AS source, name FROM customers
UNION ALL
SELECT 'lead' AS source, name FROM leads;❌ Don’t Do This:
-- Use UNION when UNION ALL suffices
SELECT name FROM a
UNION
SELECT name FROM b; -- ❌ slower if duplicates are fine
-- Put ORDER BY in the first query
SELECT name FROM a ORDER BY name
UNION
SELECT name FROM b; -- ❌ syntax error
-- Mismatch column counts
SELECT id, name FROM a
UNION
SELECT id FROM b; -- ❌ error
-- Mismatch column order
SELECT name, age FROM a
UNION
SELECT age, name FROM b; -- ❌ wrong resultCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Column count mismatch | Different SELECT lists | Match the number of columns |
| Type mismatch | Incompatible types | Cast to a common type |
| Wrong result from column order | Columns matched by position | Same order in all queries |
| ORDER BY error | Placed in the first query | Move to the end |
| Slow query | Used UNION unnecessarily | Use UNION ALL |
| Duplicate matched rows | Used UNION ALL in the full-outer workaround | Use UNION |
Real-World Examples
1. Combine Two Sources
SELECT name FROM customers UNION SELECT name FROM leads;2. Keep Duplicates
SELECT name FROM customers UNION ALL SELECT name FROM leads;3. Sort the Result
SELECT name FROM a UNION ALL SELECT name FROM b ORDER BY name;4. Label the Source
SELECT 'customer' AS source, name FROM customers
UNION ALL
SELECT 'lead' AS source, name FROM leads;5. Three Sources
SELECT id FROM jan UNION ALL
SELECT id FROM feb UNION ALL
SELECT id FROM mar;6. Full Outer Join Simulation
SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.id FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;7. Top-N from Each Source
SELECT * FROM (SELECT name FROM a ORDER BY score DESC LIMIT 5) t1
UNION ALL
SELECT * FROM (SELECT name FROM b ORDER BY score DESC LIMIT 5) t2;8. Distinct Values Across Sources
SELECT DISTINCT name FROM (
SELECT name FROM a UNION ALL SELECT name FROM b
) AS combined;9. Union with a Filter
SELECT name FROM a WHERE active
UNION ALL
SELECT name FROM b WHERE active;10. Union of Aggregates
SELECT 'total' AS label, COUNT(*) FROM a
UNION ALL
SELECT 'total' AS label, COUNT(*) FROM b;Visual
UNION vs UNION ALL
┌──────────────────────────────────────────────────────────────┐
│ customers: Alice, Bob │
│ leads: Bob, Carol │
│ │
│ UNION: │
│ ┌────────┐ │
│ │ Alice │ │
│ │ Bob │ ← appears once │
│ │ Carol │ │
│ └────────┘ │
│ │
│ UNION ALL: │
│ ┌────────┐ │
│ │ Alice │ │
│ │ Bob │ ← appears twice │
│ │ Bob │ │
│ │ Carol │ │
│ └────────┘ │
└──────────────────────────────────────────────────────────────┘Column Matching by Position
┌──────────────────────────────────────────────────────────────┐
│ CORRECT: │
│ SELECT name, age FROM a │
│ UNION │
│ SELECT name, age FROM b │
│ └── Column 1 matches name, column 2 matches age │
│ │
│ WRONG: │
│ SELECT name, age FROM a │
│ UNION │
│ SELECT age, name FROM b │
│ └── Column 1 matches name to age (wrong!) │
│ │
│ Columns are matched by position, not by name. │
└──────────────────────────────────────────────────────────────┘ORDER BY Placement
┌──────────────────────────────────────────────────────────────┐
│ VALID: │
│ SELECT name FROM a │
│ UNION ALL │
│ SELECT name FROM b │
│ ORDER BY name; │
│ └── Sorts the entire result │
│ │
│ INVALID: │
│ SELECT name FROM a ORDER BY name │
│ UNION ALL │
│ SELECT name FROM b; │
│ └── Syntax error │
│ │
│ To sort one query before the union, wrap it in a subquery. │
└──────────────────────────────────────────────────────────────┘Full Outer Join Simulation
┌──────────────────────────────────────────────────────────────┐
│ LEFT JOIN → matched + left-only │
│ UNION │
│ RIGHT JOIN → matched + right-only │
│ └── UNION removes the duplicate matched rows │
│ │
│ Result: matched + left-only + right-only │
│ └── Equivalent to a FULL OUTER JOIN │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| UNION | Combines rows, removes duplicates |
| UNION ALL | Combines rows, keeps duplicates |
| Column count | Must match |
| Column types | Must be compatible |
| Column order | Matched by position |
| Column names | From the first query |
| ORDER BY | Only at the end |
| Performance | UNION ALL faster |
| Full outer simulation | LEFT JOIN UNION RIGHT JOIN |
| Default choice | UNION ALL |
Key takeaways:
- UNION combines rows from two or more SELECT statements. Unlike a join, which combines columns, a union appends rows. The queries must have the same number of columns with compatible types, and the columns are matched by position.
- UNION removes duplicates; UNION ALL keeps them. The choice between them is about whether duplicates must be removed.
UNION ALLis faster because it performs no comparison. - The column names come from the first query. The second query’s column aliases are ignored. To label the combined result, alias the columns in the first query.
- ORDER BY applies to the entire combined result. It can only appear at the end. To sort an individual query before the union, wrap it in a subquery.
- UNION ALL is the default choice. Use it unless duplicates must be removed. Even when duplicates exist, they may be acceptable, and avoiding the sort is worth it.
- The full outer join simulation uses UNION. The
LEFT JOIN UNION RIGHT JOINpattern produces the equivalent of a full outer join in databases that do not support one. TheUNIONremoves the duplicate matched rows. - Add a literal column to distinguish sources. When the source table’s identity matters, a literal column like
'active'or'archived'labels each row.
Remember: The UNION operator combines rows from multiple queries into a single result set. It is the SQL mechanism for appending results, and it complements the join, which combines columns. The two forms — UNION and UNION ALL — differ in whether duplicates are removed, and the choice is about correctness and performance. Use UNION ALL by default, and UNION only when duplicates must be removed. The queries must have the same number of columns with compatible types, and the columns are matched by position, not by name. The column names come from the first query, and ORDER BY applies to the entire combined result at the end.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!