SQL UNION vs UNION ALL Explained

SQL UNION vs UNION ALL — Easy Guide & Examples



⭐ Learn SQL through Job-Ready Project-Based Hands-on Courses! →



When you need to combine results from two or more SELECT queries, SQL gives you two closely related operators: UNION and UNION ALL. They look similar but behave differently — especially with duplicates and performance. This guide explains both commands with clear examples, shows when to use each one, and includes tips for optimizing queries that combine multiple result sets.



What do UNION and UNION ALL do?


📽 Watch Video!

Both UNION and UNION ALL combine the results of two or more SELECT statements into a single result set. The key differences:



  • UNION removes duplicate rows from the combined result set (performs a distinct operation).


  • UNION ALL preserves duplicates — it simply concatenates the results.



Basic Syntax



-- UNION (duplicates removed)
SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;

-- UNION ALL (duplicates preserved)
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;


Important rules:



  • Both SELECTs must have the same number of columns.


  • Corresponding columns should have compatible data types.


  • Column names in the final result usually come from the first SELECT.


Example: Combining Customer Lists



Imagine two tables with customer emails from two systems:



-- Table: CustomersA
email
alice@example.com
bob@example.com

-- Table: CustomersB
email
bob@example.com
carol@example.com


Using UNION:



SELECT email FROM CustomersA
UNION
SELECT email FROM CustomersB;
-- Result:
-- alice@example.com
-- bob@example.com
-- carol@example.com


Using UNION ALL:



SELECT email FROM CustomersA
UNION ALL
SELECT email FROM CustomersB;
-- Result:
-- alice@example.com
-- bob@example.com
-- bob@example.com
-- carol@example.com


Choose UNION when you need a unique list. Choose UNION ALL when duplicates are meaningful or performance matters.



Performance: UNION vs UNION ALL



UNION is usually slower because it performs an implicit DISTINCT operation across the combined rows — this involves sorting or hashing to remove duplicates. UNION ALL merely concatenates results, so it’s faster and uses less memory.



Performance rule of thumb:



  • Use UNION ALL when you know the queries do not produce duplicate rows, or when duplicates are acceptable.


  • Use UNION when duplicates must be removed and the final result must be unique.


Example: Performance-aware usage



-- If source tables are guaranteed disjoint (no overlap), prefer UNION ALL
SELECT id, name FROM Sales_2024
UNION ALL
SELECT id, name FROM Sales_2025;


If you can't guarantee disjoint sets but still want performance, consider deduplicating upstream (e.g., via WHERE conditions, joins, or staging tables) before combining.



Ordering the Combined Results



To sort the combined result set, add ORDER BY after the final SELECT. Use column aliases from the first SELECT or ordinal positions (though names are clearer).



SELECT product_name, price FROM StoreA
UNION ALL
SELECT product_name, price FROM StoreB
ORDER BY price DESC;


Note: Some databases require parentheses or subqueries for complex UNION chains when using ORDER BY or LIMIT.



UNION with Additional Columns



You can add source-identifying columns to know where rows came from:



SELECT 'A' AS source, id, name FROM CustomersA
UNION ALL
SELECT 'B' AS source, id, name FROM CustomersB;


This is handy for auditing, debugging, or reporting where the same rows exist in multiple sources.



UNION & NULLs



NULL values are treated as equal in the context of duplicate removal with UNION. If two rows are identical except one column is NULL in both, one row remains after UNION.



Common Pitfalls & Gotchas



  • Different column counts:

    Both SELECTs must return the same number of columns.


  • Incompatible types:

    Avoid combining totally incompatible types; use CAST if needed.


  • Unexpected duplicates:

    UNION removes exact duplicates; if rows differ slightly (extra whitespace, case differences) they won’t be removed.


  • Column names come from first SELECT:

    Use aliases in the first SELECT to control final column names.


Advanced: Using UNION in Views and CTEs



UNION and UNION ALL are commonly used inside views or CTEs to create a unified dataset from multiple sources.



WITH AllOrders AS (
  SELECT OrderID, CustomerID, OrderDate FROM OnlineOrders
  UNION ALL
  SELECT OrderID, CustomerID, OrderDate FROM RetailOrders
)
SELECT CustomerID, COUNT(*) AS TotalOrders
FROM AllOrders
GROUP BY CustomerID
ORDER BY TotalOrders DESC;


This pattern is useful to create a consolidated table for downstream analysis.



When to Use UNION vs UNION ALL — Decision Guide



Use CasePrefer
Need unique rowsUNION
Performance-critical, no duplicates expectedUNION ALL
Combining logs where duplicates are meaningfulUNION ALL
Combining disjoint partitions (e.g., per-year tables)UNION ALL


Practice Exercises



  1. Combine two customer lists and return unique emails using UNION.


  2. Combine monthly sales tables with UNION ALL and calculate total sales per customer.


  3. Create a CTE using UNION ALL to combine event logs, then find the top event types.


  4. Compare results of UNION vs UNION ALL when tables have overlapping rows — observe duplicates.


Examples: Real-World Queries



Example 1 — Unique product SKUs across suppliers



SELECT SKU, ProductName FROM SupplierA
UNION
SELECT SKU, ProductName FROM SupplierB;


Example 2 — Fast aggregation from partitioned tables



SELECT CustomerID, SUM(Total) AS Revenue
FROM (
  SELECT CustomerID, Total FROM Orders_2023
  UNION ALL
  SELECT CustomerID, Total FROM Orders_2024
) AS AllOrders
GROUP BY CustomerID;


FAQ — UNION vs UNION ALL



Q: Can I use ORDER BY with UNION?



A: Yes — place ORDER BY after the final SELECT to sort the combined result. For complex chains, wrap them in a subquery.



Q: Does UNION remove rows with partial differences?



A: No — UNION removes only rows that are exactly equal across all selected columns. Small differences (case, whitespace) keep rows separate.



Q: Which is faster: UNION or UNION ALL?



A: UNION ALL is almost always faster because it skips duplicate elimination. Use UNION ALL when duplicates are not an issue.



Conclusion



UNION and UNION ALL are essential tools for combining datasets. Use UNION when you need uniqueness, and UNION ALL when you want speed or to preserve all rows. Think about your data (are duplicates possible or meaningful?) and choose the operator that matches your intent. Practice the exercises and apply these patterns in reports and CTEs for clean, maintainable SQL.



⭐ Need an SQL Certificate, CEUs, or a FREE start with Hands-On Practice! → Get Started Here!