Lesson 8: INNER JOIN

So far, we've only queried data from a single table. But in practice, you often need to combine data from multiple tables, like finding 'the name of the customer who placed an order'. In this lesson, you'll learn how to connect tables using INNER JOIN.

JOIN combines rows from two or more tables based on related columns. INNER JOIN returns only the rows that match in both tables -- unmatched rows are excluded from the result.

INNER JOIN returns only rows that match in both tables. Park (ID:3) has no orders, so they are excluded from the result.

Joining Two Tables

The syntax is FROM table_a INNER JOIN table_b ON table_a.key = table_b.key. The ON clause specifies the matching condition.

-- Query orders with customer names
SELECT
    o.order_number,
    c.name        AS customer_name,
    o.status,
    o.total_amount
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
ORDER BY o.ordered_at DESC
LIMIT 5;

Result:

Table aliases (o, c) make queries shorter and ON conditions easier to read. While not required, they are strongly recommended when joining multiple tables.

Why INNER JOIN Excludes Unmatched Rows

Customers who have never placed an order do not appear in the result -- because there is no matching row in orders. Conversely, thanks to foreign key constraints, every order must have a customer, so all orders are matched.

-- Check: Are there any orders without a customer?
SELECT COUNT(*) AS orders_without_customer
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

Joining Three or More Tables

Chain additional JOIN clauses. Each one connects to the already-combined tables.

-- Include product name and category name in order items
SELECT
    oi.id           AS item_id,
    o.order_number,
    p.name          AS product_name,
    cat.name        AS category,
    oi.quantity,
    oi.unit_price
FROM order_items AS oi
INNER JOIN orders     AS o   ON oi.order_id   = o.id
INNER JOIN products   AS p   ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
ORDER BY o.ordered_at DESC
LIMIT 6;

Result:

Aggregation After JOIN

Perform the join first, then aggregate. You can use columns from joined tables for grouping.

-- Total revenue by product category
SELECT
    cat.name        AS category,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity)     AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS gross_revenue
FROM order_items AS oi
INNER JOIN orders     AS o   ON oi.order_id   = o.id
INNER JOIN products   AS p   ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE o.status IN ('delivered', 'confirmed')
GROUP BY cat.name
ORDER BY gross_revenue DESC
LIMIT 8;

Result:

Filtering Joined Tables

You can apply WHERE conditions to any table participating in the join.

-- Orders over 1,000,000 from VIP customers in 2024
SELECT
    c.name          AS customer_name,
    o.order_number,
    o.total_amount,
    o.ordered_at
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE c.grade = 'VIP'
  AND o.total_amount > 1000
  AND o.ordered_at LIKE '2024%'
ORDER BY o.total_amount DESC;

Summary

Practice Problems

Problem 1

Query each review with the customer's name and product's name. Return review_id, customer_name, product_name, rating, created_at, sorted by rating descending then created_at descending, limited to 10 rows.

SELECT
    r.id          AS review_id,
    c.name        AS customer_name,
    p.name        AS product_name,
    r.rating,
    r.created_at
FROM reviews AS r
INNER JOIN customers AS c ON r.customer_id = c.id
INNER JOIN products  AS p ON r.product_id  = p.id
ORDER BY r.rating DESC, r.created_at DESC
LIMIT 10;

Problem 2

Find the number of unique customers per payment method. Return method and unique_customers, sorted by unique_customers descending.

SELECT
    p.method,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM payments AS p
INNER JOIN orders AS o ON p.order_id = o.id
WHERE p.status = 'completed'
GROUP BY p.method
ORDER BY unique_customers DESC;

Problem 3

Find the top 5 customers by total purchase amount (sum of total_amount excluding cancelled/returned orders). Return customer_name, grade, order_count, total_spent.

SELECT
    c.name   AS customer_name,
    c.grade,
    COUNT(o.id)         AS order_count,
    SUM(o.total_amount) AS total_spent
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY c.id, c.name, c.grade
ORDER BY total_spent DESC
LIMIT 5;

Problem 4

Query order_number, customer_name, and status, filtering only orders with status 'shipped'. Sort by ordered_at descending and limit to 10 rows.

SELECT
    o.order_number,
    c.name   AS customer_name,
    o.status
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.status = 'shipped'
ORDER BY o.ordered_at DESC
LIMIT 10;

Problem 5

Join the shipping and orders tables to query order_number, carrier, tracking_number, and delivered_at for delivered orders. Sort by delivered_at descending and limit to 10 rows.

SELECT
    o.order_number,
    s.carrier,
    s.tracking_number,
    s.delivered_at
FROM shipping AS s
INNER JOIN orders AS o ON s.order_id = o.id
WHERE s.status = 'delivered'
ORDER BY s.delivered_at DESC
LIMIT 10;

Problem 6

Query product name (products.name), supplier name (suppliers.company_name), quantity, and unit price from order_items. Join 3 tables and sort by unit price descending, limited to 10 rows.

SELECT
    p.name          AS product_name,
    sup.company_name AS supplier_name,
    oi.quantity,
    oi.unit_price
FROM order_items AS oi
INNER JOIN products  AS p   ON oi.product_id  = p.id
INNER JOIN suppliers AS sup ON p.supplier_id   = sup.id
ORDER BY oi.unit_price DESC
LIMIT 10;

Problem 7

Find the total order count (order_count) and total quantity sold (total_qty) per supplier. Return company_name, order_count, total_qty, sorted by total_qty descending.

SELECT
    sup.company_name,
    COUNT(DISTINCT oi.order_id) AS order_count,
    SUM(oi.quantity)            AS total_qty
FROM order_items AS oi
INNER JOIN products  AS p   ON oi.product_id = p.id
INNER JOIN suppliers AS sup ON p.supplier_id  = sup.id
GROUP BY sup.id, sup.company_name
ORDER BY total_qty DESC;

Problem 8

Find the order count (order_count) and total revenue (total_revenue) per assigned staff member. Exclude cancelled/returned orders. Return staff_name, department, order_count, total_revenue. Sort by total_revenue descending and return the top 10.

SELECT
    st.name        AS staff_name,
    st.department,
    COUNT(o.id)         AS order_count,
    SUM(o.total_amount) AS total_revenue
FROM orders AS o
INNER JOIN staff AS st ON o.staff_id = st.id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY st.id, st.name, st.department
ORDER BY total_revenue DESC
LIMIT 10;

Problem 9

Query the reviewer's name (customers.name), reviewed product name (products.name), rating, and review date (reviews.created_at). Only include reviews with a rating of 5. Sort by reviews.created_at descending and limit to 10 rows.

SELECT
    c.name        AS customer_name,
    p.name        AS product_name,
    r.rating,
    r.created_at
FROM reviews AS r
INNER JOIN customers AS c ON r.customer_id = c.id
INNER JOIN products  AS p ON r.product_id  = p.id
WHERE r.rating = 5
ORDER BY r.created_at DESC
LIMIT 10;

Problem 10

Find the total order amount (total_revenue) and order count (order_count) per category. Return categories.name, order_count, total_revenue, targeting only top-level categories (depth = 0). Sort by total_revenue descending.

SELECT
    cat.name       AS category_name,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items AS oi
INNER JOIN orders     AS o   ON oi.order_id   = o.id
INNER JOIN products   AS p   ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE cat.depth = 0
ORDER BY total_revenue DESC;

Scoring Guide

Problem Areas:

Next: Lesson 9: LEFT JOIN