Skip to main content

UNION ALL

Learning Focus

Use this lesson to understand UNION ALL with practical syntax and examples.

Concept Overview​

What is UNION ALL?​

UNION ALL is a SQL operator used to combine the results of two or more SELECT statements. Unlike UNION, which removes duplicate rows, UNION ALL includes all rows, even duplicates.

Why is this Important?​

  • Helps consolidate data from multiple sources or tables.
  • Ideal for combining large datasets quickly, without performance costs from deduplication.
  • Useful in reporting, merging logs, or appending rows from separate data queries.

Where it Fits in the Database Ecosystem​

  • Part of Set Operators in SQL.
  • Complements Joins but operates differently:
    • Joins combine rows based on related columns (horizontal merge).
    • UNION/UNION ALL stack rows vertically.

Basic Syntax & Rules​

Syntax​

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Key Rules​

  • Number of columns in each SELECT must be the same.
  • Corresponding columns must have compatible data types.
  • Column names from the first SELECT are used in the result set.

Limitations & Considerations​

  • No automatic duplicate removal (which is the intended behavior).
  • Use ORDER BY at the end once, after the last SELECT.
  • Cannot use LIMIT in intermediate SELECTs without parentheses.

Step-by-Step Examples​

Example 1: Basic - Combine Two Tables​

Tables​

-- Customers_USA
+----+----------+
| ID | Name     |
+----+----------+
| 1  | Alice    |
| 2  | Bob      |
+----+----------+

-- Customers_Canada
+----+----------+
| ID | Name     |
+----+----------+
| 3  | Claire   |
| 4  | Bob      |
+----+----------+

Query​

SELECT Name FROM Customers_USA
UNION ALL
SELECT Name FROM Customers_Canada;

Output​

+--------+
| Name   |
+--------+
| Alice  |
| Bob    |
| Claire |
| Bob    |   <-- duplicate is kept
+--------+

Example 2: Combining Tables with Different Aliases​

SELECT Name, 'USA' AS Country FROM Customers_USA
UNION ALL
SELECT Name, 'Canada' AS Country FROM Customers_Canada;

Output​

+--------+---------+
| Name   | Country |
+--------+---------+
| Alice  | USA     |
| Bob    | USA     |
| Claire | Canada  |
| Bob    | Canada  |
+--------+---------+

Example 3: Using ORDER BY​

SELECT Name FROM Customers_USA
UNION ALL
SELECT Name FROM Customers_Canada
ORDER BY Name;

Practical Use Cases​

1. Merging Logs from Multiple Systems​

SELECT log_time, message FROM server1_logs
UNION ALL
SELECT log_time, message FROM server2_logs;

2. Monthly Sales Data Append​

SELECT * FROM sales_january
UNION ALL
SELECT * FROM sales_february;

3. Multi-Region Customer View​

SELECT Name, 'USA' AS Region FROM customers_usa
UNION ALL
SELECT Name, 'EU' AS Region FROM customers_eu;

4. Reporting Across Years​

SELECT product_id, revenue, '2023' AS year FROM sales_2023
UNION ALL
SELECT product_id, revenue, '2024' AS year FROM sales_2024;

Common Mistakes & Troubleshooting​

1. Mismatched Column Counts​

-- [X] Error: SELECTs have different number of columns
SELECT id, name FROM users
UNION ALL
SELECT id FROM employees;

[OK] Fix:

SELECT id, name FROM users
UNION ALL
SELECT id, NULL FROM employees;

2. Unexpected Duplicates​

Solution: Use UNION if you want to remove them.

3. Incorrect ORDER BY Placement​

[OK] Only allowed at the end:

SELECT name FROM a
UNION ALL
SELECT name FROM b
ORDER BY name;

4. Type Mismatch​

SELECT id FROM table1
UNION ALL
SELECT date_column FROM table2;  -- [X] incompatible types

[OK] Make sure data types match or use CAST.

Best Practices​

  • Use UNION ALL when duplicates are acceptable and performance matters.
  • Use UNION if deduplication is essential.
  • Avoid complex ORDER BY across large UNION ALL queries unless necessary.
  • Use aliases to indicate data origin when combining multiple datasets.
  • Benchmark performance when dealing with large datasets.

Hands-On Practice​

Sample Data Setup​

CREATE TABLE Employees_2023 (id INT, name VARCHAR(50));
CREATE TABLE Employees_2024 (id INT, name VARCHAR(50));

INSERT INTO Employees_2023 VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Employees_2024 VALUES (3, 'Charlie'), (2, 'Bob');

Exercise 1: Simple Combine​

Task: Combine all employee names from both years.

-- Expected Output:
-- Alice
-- Bob
-- Charlie
-- Bob

-- Write your query here:

Exercise 2: Add Source Year​

Task: Show employee name and year.

-- Expected Output:
-- Alice, 2023
-- Bob, 2023
-- Charlie, 2024
-- Bob, 2024

-- Write your query here:

Exercise 3: Ordered Result​

Task: Combine and order alphabetically by name.

Connection to Other Concepts​

ConceptRelation
UNIONSame as UNION ALL, but removes duplicates
JOINCombines rows horizontally
SELECTFoundation for UNION ALL usage
ORDER BYOnly applied once, at the end

Prerequisites​

  • Basic SQL SELECT
  • Understanding data types
  • Familiarity with tables and rows

What to Learn Next​

  • Performance optimization with indexes
  • Advanced set operators (INTERSECT, EXCEPT)
  • Views and subqueries for dynamic unions

Visual Learning Diagram (Mermaid)​

graph TD
classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
classDef highlight stroke-width:4px

A[Basic SELECT] --> B[Column Matching Rules]
B --> C[UNION]
B --> D{{UNION ALL}}
D --> E[ORDER BY Usage]
D --> F[Combining Datasets]
E --> G(Correct Placement)
F --> H[Real-world Applications]

C --> I[Duplicates Removed]
D --> J[Duplicates Included]

I --> K[Data Cleansing]
J --> L[Reporting Logs]
J --> M[Analytics]
J --> N[Multi-year Queries]

D --> O[Practice Exercises]
O --> P[Sample Data]
O --> Q[Merge Output]

Q --> R[Performance Tips]

class A,B,C,E,F,G,H,I,J,K,L,M,N,O,P,Q,R allNodes
class D highlight

Common Pitfalls​

PitfallConsequencePrevention
Executing queries without validating sample rowsLogic errors reach production data or reportsStart with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behaviorAggregations and filters return misleading resultsTest with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schemaQueries fail due to missing columns/indexesVerify structure with DESCRIBE table_name; and adapt query design

Quick Reference​

SELECT a.id, b.name FROM table_a a INNER JOIN table_b b ON a.b_id = b.id;
SELECT id FROM active_users UNION ALL SELECT id FROM archived_users;

What's Next​