Combining Results with UNION

Stacking Results, Not Joining

Before we learn how to link tables side by side, there’s another way to combine data from multiple tables: UNION.

UNION doesn’t “link” rows together. Instead, it stacks results on top of each other. Think of UNION as “add more rows to the same shape of table.”

Later, when we talk about JOINs, we’ll see the opposite pattern:

• JOINs add more columns about each row
• UNION adds more rows with the same columns

For now, keep one idea in your head: UNION = same columns, more rows.

When You’ll Use UNION

UNION comes up when you have data split across tables that should really be together:

• Separate tables for different regions (us_orders, eu_orders)
• Historical data in yearly tables (orders_2022, orders_2023)
• Different data sources with the same structure (app_signups, web_signups)
• Building a lookup list from multiple places

Basic UNION Syntax

SELECT column1, column2
FROM table_a

UNION

SELECT column1, column2
FROM table_b;

Simple: two SELECT statements with UNION between them. The results stack on top of each other.

A Real Example

Say you need all employees from HR and Admin in one list.

Notice the added type column. That’s a common pattern when you’re combining tables: add a source identifier so you know where each row came from.

Make the Friends Network Symmetric

The Column Matching Rule

UNION has a strict requirement: both SELECTs must return the same number of columns, and the data types must be compatible.

-- This works: both have 2 columns (text, text)
SELECT name, email
FROM employees

UNION

SELECT name, email
FROM contractors;

-- This FAILS: different number of columns
SELECT name, email, salary
FROM employees

UNION

SELECT name, email
FROM contractors; -- Error!

If one table has more columns than you need, just don’t select them. If one table is missing a column, you can add a placeholder:

SELECT name, email, salary
FROM employees

UNION

SELECT name, email, NULL AS salary
FROM contractors;

All Possible Wine Varieties

UNION vs UNION ALL

• UNION removes duplicate rows (expensive)
• UNION ALL keeps everything (fast)

In practice, UNION ALL is what you want 90% of the time. Duplicates usually matter — they represent real data. If John ordered from both the US and EU sites, you want both orders in your analysis.

Duplicate HR Department Employees

Sorting UNION Results

ORDER BY goes at the very end, after all the UNIONs:

SELECT name, signup_date
FROM web_users

UNION ALL

SELECT name, signup_date
FROM app_users
ORDER BY signup_date DESC;

You can’t ORDER BY in the middle. SQL processes all the UNIONs first, then sorts the combined result.

Pattern 1: Multi-Region Data

SELECT
  order_id,
  customer_id,
  amount,
  'US' AS region
FROM us_orders

UNION ALL

SELECT
  order_id,
  customer_id,
  amount,
  'EU' AS region
FROM eu_orders

UNION ALL

SELECT
  order_id,
  customer_id,
  amount,
  'APAC' AS region
FROM apac_orders;

Pattern 2: Creating a Reference List

-- Build a list of all people we might need to contact
SELECT email
FROM customers

UNION

SELECT email
FROM newsletter_subscribers

UNION

SELECT email
FROM lead_forms;

Here UNION (not ALL) makes sense because you only want each email once.

Key Takeaways

• UNION stacks rows vertically (vs JOIN, which combines columns horizontally)
• Both SELECTs must have the same number of columns with compatible types
• UNION removes duplicates, UNION ALL keeps them (prefer UNION ALL)
• ORDER BY goes at the end, applying to the combined result
• Add a source column to track where each row came from

What’s Next

Now you know how to stack data from similar tables. But most of the time, you need to combine related data from different tables — customers with their orders, products with their categories. That’s where JOINs come in, starting with INNER JOIN.