Lesson 14: UNION

In Lesson 13, we learned numeric, conversion, and conditional functions. So far we've queried data with a single SELECT. But sometimes you need to combine results from multiple queries, like "top 5 products + bottom 5 products". In this lesson, we learn how to combine results with UNION.

UNION stacks the results of two or more SELECT statements vertically. Each query must return the same number of columns, and the corresponding column types must be compatible. Column names come from the first query.

UNION combines two query results vertically. Column count and types must match.

UNION vs. UNION ALL

Use UNION ALL when you know there are no duplicates, or when you want to count all occurrences.

Basic UNION

-- Combine VIP and GOLD customers into one list
-- (No duplicates possible from the same table, but UNION removes them just in case)
SELECT id, name, grade FROM customers WHERE grade = 'VIP'
UNION
SELECT id, name, grade FROM customers WHERE grade = 'GOLD'
ORDER BY name;

This result is identical to WHERE grade IN ('VIP', 'GOLD'), but UNION's true power shows when combining different tables.

Combining Different Tables

A typical UNION use case: creating a unified activity feed or report from multiple source tables.

-- Activity log combining a specific customer's orders and reviews
SELECT
    'order'   AS activity_type,
    customer_id,
    ordered_at AS activity_date,
    CAST(total_amount AS TEXT) AS detail
FROM orders
WHERE customer_id = 42

UNION ALL

SELECT
    'review'  AS activity_type,
    customer_id,
    created_at AS activity_date,
    '별점: ' || CAST(rating AS TEXT) AS detail
FROM reviews
WHERE customer_id = 42

ORDER BY activity_date DESC;

SELECT
    'order'   AS activity_type,
    customer_id,
    ordered_at AS activity_date,
    CAST(total_amount AS CHAR) AS detail
FROM orders
WHERE customer_id = 42

UNION ALL

SELECT
    'review'  AS activity_type,
    customer_id,
    created_at AS activity_date,
    CONCAT('별점: ', rating) AS detail
FROM reviews
WHERE customer_id = 42

ORDER BY activity_date DESC;

SELECT
    'order'   AS activity_type,
    customer_id,
    ordered_at AS activity_date,
    total_amount::text AS detail
FROM orders
WHERE customer_id = 42

UNION ALL

SELECT
    'review'  AS activity_type,
    customer_id,
    created_at AS activity_date,
    '별점: ' || rating::text AS detail
FROM reviews
WHERE customer_id = 42

ORDER BY activity_date DESC;

Result:

-- All 2024 complaint and return events
SELECT
    'complaint'         AS event_type,
    c.customer_id,
    c.created_at        AS event_date,
    c.title             AS description
FROM complaints AS c
WHERE c.created_at LIKE '2024%'

UNION ALL

SELECT
    'return'            AS event_type,
    o.customer_id,
    r.created_at        AS event_date,
    r.reason            AS description
FROM returns AS r
INNER JOIN orders AS o ON r.order_id = o.id
WHERE r.created_at LIKE '2024%'

ORDER BY event_date DESC
LIMIT 10;

Creating Rollup Reports with UNION ALL

-- Category revenue + add total row
SELECT
    0 AS sort_key,
    cat.name AS category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi
INNER JOIN products   AS p   ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
INNER JOIN orders     AS o   ON oi.order_id   = o.id
WHERE o.status IN ('delivered', 'confirmed')
  AND o.ordered_at LIKE '2024%'
GROUP BY cat.name

UNION ALL

SELECT
    1 AS sort_key,
    '합계' AS category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi
INNER JOIN orders AS o ON oi.order_id = o.id
WHERE o.status IN ('delivered', 'confirmed')
  AND o.ordered_at LIKE '2024%'

ORDER BY 1, 3 DESC;

SQLite note: CASE expressions cannot be used directly in ORDER BY with UNION / UNION ALL, and using column position numbers instead of aliases is safer. Above, ORDER BY 1, 3 DESC means first column (sort_key) ascending, third column (revenue) descending.

Result (partial):

INTERSECT -- Intersection

INTERSECT returns only rows that exist in both query results.

-- Customers who both wrote reviews and filed complaints
SELECT customer_id FROM reviews
INTERSECT
SELECT customer_id FROM complaints;

SQLite note: INTERSECT is supported in SQLite 3.34.0+ (2020-12). Also available in MySQL 8.0.31+ and all PostgreSQL versions.

-- Application: VIP customers who ordered in the last 6 months
SELECT id FROM customers WHERE grade = 'VIP'
INTERSECT
SELECT DISTINCT customer_id FROM orders
WHERE ordered_at >= DATE('now', '-6 months');

EXCEPT / MINUS -- Set Difference

EXCEPT returns rows from the first query result that are not in the second query result. In Oracle, it's called MINUS.

-- Customers who wrote reviews but never filed complaints
SELECT customer_id FROM reviews
EXCEPT
SELECT customer_id FROM complaints;

-- Orders that were placed but shipping never started
SELECT id FROM orders WHERE status = 'confirmed'
EXCEPT
SELECT order_id FROM shipping;

EXCEPT returns the same result as a NOT IN subquery or LEFT JOIN ... IS NULL anti-join, but the set operation syntax is more readable.

Comparing UNION vs. INTERSECT vs. EXCEPT

All three operations remove duplicates. To keep duplicates, use UNION ALL, INTERSECT ALL, EXCEPT ALL (support varies by database).

Summary

Practice Problems

Problem 1

Combine VIP grade customers' names and grades with GOLD grade customers' names and grades using UNION into a single list. Sort by name.

SELECT name, grade FROM customers WHERE grade = 'VIP'
UNION
SELECT name, grade FROM customers WHERE grade = 'GOLD'
ORDER BY name;

Problem 2

Combine all active product (is_active = 1) names and all category names using UNION to create a deduplicated name list. The result column should be name.

SELECT name FROM products WHERE is_active = 1
UNION
SELECT name FROM categories
ORDER BY name;

Problem 3

Create a "negative events" list combining cancelled and returned orders from 2023-2024. Use UNION ALL with event_type ('cancellation' or 'return'), order_number, customer_id, event_date (use cancelled_at for cancellations, completed_at for returns). Sort by event_date descending.

SELECT
    'cancellation'  AS event_type,
    order_number,
    customer_id,
    cancelled_at    AS event_date
FROM orders
WHERE status = 'cancelled'
  AND cancelled_at BETWEEN '2023-01-01' AND '2024-12-31 23:59:59'

UNION ALL

SELECT
    'return'        AS event_type,
    order_number,
    customer_id,
    completed_at    AS event_date
FROM orders
WHERE status = 'returned'
  AND completed_at BETWEEN '2023-01-01' AND '2024-12-31 23:59:59'

ORDER BY event_date DESC;

Problem 4

Create a "customer feedback" list combining 2024 reviews and 2024 product Q&A (questions only, parent_id IS NULL). Use UNION ALL with feedback_type ('review' or 'qna'), product_id, customer_id, created_at. Sort by created_at descending, showing only the top 20.

SELECT
    'review' AS feedback_type,
    product_id,
    customer_id,
    created_at
FROM reviews
WHERE created_at LIKE '2024%'

UNION ALL

SELECT
    'qna' AS feedback_type,
    product_id,
    customer_id,
    created_at
FROM product_qna
WHERE parent_id IS NULL
  AND created_at LIKE '2024%'

ORDER BY created_at DESC
LIMIT 20;

Problem 5

Aggregate count by payment method, then add a total row with UNION ALL. Display 'Total' for the total row's method. Target only payments where status = 'completed'.

SELECT
    0 AS sort_key,
    method,
    COUNT(*) AS tx_count
FROM payments
WHERE status = 'completed'
GROUP BY method

UNION ALL

SELECT
    1 AS sort_key,
    '합계' AS method,
    COUNT(*) AS tx_count
FROM payments
WHERE status = 'completed'

ORDER BY sort_key, tx_count DESC;

Problem 6

Aggregate count by customer grade, then add a grand total row ('Total') with UNION ALL. Target only is_active = 1 customers. Sort so the total row comes last.

SELECT
    0 AS sort_key,
    grade,
    COUNT(*) AS cnt
FROM customers
WHERE is_active = 1
GROUP BY grade

UNION ALL

SELECT
    1 AS sort_key,
    '전체' AS grade,
    COUNT(*) AS cnt
FROM customers
WHERE is_active = 1

ORDER BY sort_key, cnt DESC;

Problem 7

Create a customer engagement summary. Use UNION ALL to aggregate total orders, total reviews, and total complaints per customer. Wrap the union result as a subquery (derived table) to aggregate into one row per customer, and return the top 10 by total activity count.

SELECT
    customer_id,
    SUM(activity_count) AS total_activity
FROM (
    SELECT customer_id, COUNT(*) AS activity_count
    FROM orders GROUP BY customer_id

    UNION ALL

    SELECT customer_id, COUNT(*) AS activity_count
    FROM reviews GROUP BY customer_id

    UNION ALL

    SELECT customer_id, COUNT(*) AS activity_count
    FROM complaints GROUP BY customer_id
) AS all_activity
GROUP BY customer_id
ORDER BY total_activity DESC
LIMIT 10;

Problem 8

Aggregate count and average amount by order status, then add a total row with UNION ALL. Wrap the result as a subquery to calculate pct (the percentage each status count represents of the total, to 1 decimal place).

SELECT
    status,
    order_count,
    avg_amount,
    ROUND(100.0 * order_count / SUM(order_count) OVER (), 1) AS pct
FROM (
    SELECT
        0 AS sort_key,
        status,
        COUNT(*)            AS order_count,
        ROUND(AVG(total_amount), 2) AS avg_amount
    FROM orders
    GROUP BY status

    UNION ALL

    SELECT
        1 AS sort_key,
        '합계' AS status,
        COUNT(*)            AS order_count,
        ROUND(AVG(total_amount), 2) AS avg_amount
    FROM orders
) AS t
ORDER BY sort_key, order_count DESC;

Problem 9

Aggregate active and inactive product counts per supplier separately, combine with UNION ALL, then wrap as a subquery to create one row per supplier (active count, inactive count). JOIN with the suppliers table to also display company name.

SELECT
    s.company_name,
    SUM(CASE WHEN t.status_type = 'active' THEN t.cnt ELSE 0 END) AS active_count,
    SUM(CASE WHEN t.status_type = 'inactive' THEN t.cnt ELSE 0 END) AS inactive_count
FROM (
    SELECT supplier_id, 'active' AS status_type, COUNT(*) AS cnt
    FROM products WHERE is_active = 1
    GROUP BY supplier_id

    UNION ALL

    SELECT supplier_id, 'inactive' AS status_type, COUNT(*) AS cnt
    FROM products WHERE is_active = 0
    GROUP BY supplier_id
) AS t
INNER JOIN suppliers AS s ON t.supplier_id = s.id
GROUP BY s.company_name
ORDER BY active_count DESC;

Problem 10

Combine the 'highest priced product' and 'lowest priced product' per supplier into one list. Use UNION ALL with price_type ('highest' or 'lowest'), company_name, product_name, price. Sort by company_name, price_type.

SELECT
    '최고가' AS price_type,
    s.company_name,
    p.name  AS product_name,
    p.price
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
  AND p.price = (
      SELECT MAX(p2.price)
      FROM products AS p2
      WHERE p2.supplier_id = p.supplier_id AND p2.is_active = 1
  )

UNION ALL

SELECT
    '최저가' AS price_type,
    s.company_name,
    p.name  AS product_name,
    p.price
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
  AND p.price = (
      SELECT MIN(p2.price)
      FROM products AS p2
      WHERE p2.supplier_id = p.supplier_id AND p2.is_active = 1
  )

ORDER BY company_name, price_type;

Problem 11

Find the intersection of customers who wrote reviews and customers who filed complaints. Use INTERSECT and count the resulting customer_ids.

SELECT COUNT(*) AS both_count
FROM (
    SELECT customer_id FROM reviews
    INTERSECT
    SELECT customer_id FROM complaints
) AS both_active;

Problem 12

Use EXCEPT to find customers who added products to their wishlist but never placed an order. Return customer_id, sorted ascending.

SELECT customer_id FROM wishlists
EXCEPT
SELECT DISTINCT customer_id FROM orders
ORDER BY customer_id;

Scoring Guide

Problem Areas:

Next: Lesson 15: INSERT, UPDATE, DELETE