SQL UNION Operator – UNION vs UNION ALL with Examples | DbSchema

Table of Contents

1. What the SQL UNION operator does
2. UNION syntax and rules
3. UNION vs UNION ALL
4. How duplicate removal works
5. ORDER BY and WHERE with UNION
6. UNION with aggregates and subqueries
7. UNION vs JOIN vs INTERSECT vs EXCEPT
8. Common errors and performance tips
9. Explore UNION queries in DbSchema
10. FAQ
11. Conclusion

The SQL UNION operator combines the result sets of two or more SELECT statements into one result. It is one of the most important SQL set operators, alongside INTERSECT and EXCEPT.

Most confusion around UNION comes from one question: should you use UNION or UNION ALL? This article answers that clearly, shows how duplicates are handled, and explains how to debug combined queries faster in DbSchema.

What the SQL UNION operator does

Use UNION when you want to stack rows from multiple queries into one result set.

Typical use cases include:

• combining current and archived records
• merging results from similar tables such as customers_us and customers_eu
• building one report from different fact tables
• simulating a full-result comparison in databases that lack certain join features

If you need the complete query basics first, start with SQL SELECT.

UNION syntax and rules

SELECT column1, column2
FROM table1

UNION

SELECT column1, column2
FROM table2;

Both SELECT statements must:

1. return the same number of columns
2. return compatible data types in matching positions
3. use column names from the first SELECT for the final output

Basic example

SELECT name, department
FROM current_employees

UNION

SELECT name, department
FROM contractors;

If Alice appears in both source queries with the exact same selected values, UNION returns her only once.

UNION vs UNION ALL

This is the comparison most readers search for first.

SELECT name
FROM current_employees

UNION ALL

SELECT name
FROM contractors;

If Alice exists in both queries, UNION ALL returns two rows for Alice.

Rule of thumb

• use UNION when duplicates would be misleading
• use UNION ALL when duplicates are valid data or when you plan to aggregate later

How duplicate removal works

Databases compare the full selected row, not just one column.

Example: rows that look similar but are not duplicates

SELECT 'Alice' AS name, 'Engineering' AS department
UNION
SELECT 'Alice' AS name, 'Sales' AS department;

Result:

Those rows are different because the department values differ.

What about NULL values?

When duplicate elimination runs, rows are treated as duplicates if all selected column values are equal, including NULL in the same positions.

That is one reason it is helpful to preview each branch separately in DbSchema before combining them: you can see whether the “duplicate” rows are really identical or only look similar.

ORDER BY and WHERE with UNION

Each branch can have its own WHERE clause:

SELECT name, salary
FROM employees
WHERE department = 'Engineering'

UNION

SELECT name, rate AS salary
FROM contractors
WHERE rate > 5000;

ORDER BY applies to the final combined result, so place it at the end:

SELECT name, hire_date
FROM employees

UNION

SELECT name, contract_start AS hire_date
FROM contractors
ORDER BY hire_date DESC;

If you need to filter the already-combined set, wrap the UNION in a subquery:

SELECT *
FROM (
  SELECT name, salary FROM employees
  UNION ALL
  SELECT name, rate FROM contractors
) AS workers
WHERE salary > 3000;

For more sorting patterns, read SQL ORDER BY.

UNION with aggregates and subqueries

Combine aggregated results

SELECT region, SUM(amount) AS total
FROM online_sales
GROUP BY region

UNION ALL

SELECT region, SUM(amount) AS total
FROM store_sales
GROUP BY region;

Re-aggregate the combined set

SELECT region, SUM(total) AS combined_total
FROM (
  SELECT region, SUM(amount) AS total FROM online_sales GROUP BY region
  UNION ALL
  SELECT region, SUM(amount) AS total FROM store_sales GROUP BY region
) AS sales_union
GROUP BY region;

This pattern appears often in reporting pipelines. If you want to review the aggregate building blocks first, see SQL COUNT, AVG, and SUM Functions and SQL HAVING Clause.

UNION vs JOIN vs INTERSECT vs EXCEPT

UNION stacks result sets vertically. A JOIN combines columns horizontally. That distinction solves many beginner mistakes.

-- JOIN: enrich orders with customer data
SELECT o.order_id, c.customer_name
FROM Orders o
INNER JOIN Customers c
  ON o.customer_id = c.customer_id;

-- UNION: stack two compatible result sets
SELECT customer_id FROM current_customers
UNION
SELECT customer_id FROM archived_customers;

For the related set operators, continue with SQL INTERSECT Operator.

Common errors and performance tips

Column count mismatch

SELECT id, name FROM customers
UNION
SELECT id FROM suppliers;

That fails because the second query returns fewer columns.

Type mismatch

SELECT id, created_at FROM orders
UNION
SELECT customer_name, total_amount FROM invoices;

The positions do not represent compatible types or meanings.

Performance guidance

• UNION ALL is usually faster because it skips duplicate elimination
• if you plan to aggregate the unioned data later, UNION ALL is often the better first step
• filter each branch as early as possible with WHERE
• preview each branch separately before combining them

DbSchema helps here because you can run each branch independently in the SQL editor, inspect the results, and then compare the final combined output in one place.

Explore UNION queries in DbSchema

DbSchema makes UNION queries easier to validate because you can inspect both the source tables and the generated result sets without changing tools.

1. Connect through the PostgreSQL JDBC driver, MySQL JDBC driver, or SQL Server JDBC driver.
2. Use the Query Builder or SQL editor to build each SELECT branch.
3. Run the branches separately first to confirm column order and data types.
4. Add UNION or UNION ALL and inspect the merged result.
5. Document the table structures in the exported schema documentation if teammates need to understand why the branches are compatible.

That workflow is especially useful when you are unioning data from archive tables, regional tables, or staging vs production snapshots.

FAQ

What is the difference between UNION and UNION ALL? UNION removes duplicate rows. UNION ALL keeps them. UNION ALL is usually faster.

Why does UNION return fewer rows than expected?
Because duplicate elimination removed identical rows across the selected columns. Try UNION ALL if you want to see every row.

Can I use ORDER BY in each UNION branch? Usually no. Put ORDER BY after the final SELECT, or wrap each branch in a subquery when you need database-specific behavior.

Can UNION combine different tables? Yes, as long as each SELECT returns the same number of columns with compatible types.

Should I prefer UNION or JOIN? Use UNION when you want to stack similar result sets. Use JOIN when you want to combine related columns from different tables.

Conclusion

The SQL UNION operator is simple once you remember one core idea: it stacks compatible result sets. The real decision is whether duplicates should be removed or preserved, which is why UNION vs UNION ALL matters so much.

For next steps, read SQL INTERSECT Operator, SQL SELECT, and SQL ORDER BY.