SQL INNER JOIN to Get Matching Rows Between Tables

Up to this point, every query you have written pulls data from a single table. But real-world questions almost always span multiple tables. "What products did Alice order?" requires data from customers, orders, order_items, and products. "Which category generates the most revenue?" needs categories, products, and order_items. You cannot answer these questions by querying one table at a time.

JOINs are the SQL mechanism for combining rows from two or more tables based on a related column. The most common and most fundamental type is the INNER JOIN, which returns only the rows that have matching values in both tables. If a row in one table has no corresponding match in the other, it is excluded from the results entirely.

This guide covers everything you need to know about INNER JOIN: the syntax, how to visualize it, how matching works under the hood, the critical role of table aliases, and practical examples that mirror real application queries. Every example uses the ShopSmart sample database with full outputs (we defined it in a previous guide here, and then we extended it here).

Why You Need JOINs

Consider the orders table:

SELECT id, customer_id, order_date, total_amount
FROM orders
LIMIT 5;

Output:

id	customer_id	order_date	total_amount
1	1	2024-01-10	119.98
2	2	2024-01-15	89.99
3	1	2024-02-20	77.49
4	3	2024-03-05	129.99
5	4	2024-03-12	155.00

You can see order dates and amounts, but customer_id = 1 tells you nothing useful. Who is customer 1? That information lives in the customers table. To see the customer's name alongside their order, you need to combine both tables.

Without JOINs, you would have to run two separate queries and match the results manually in your application code. JOINs let the database do this matching for you, efficiently and correctly, in a single query.

INNER JOIN Syntax

The INNER JOIN syntax connects two tables using a shared column:

SELECT columns
FROM table_a
INNER JOIN table_b ON table_a.column = table_b.column;

The three essential parts are:

• FROM table_a: The first table (often called the "left" table)
• INNER JOIN table_b: The second table (the "right" table) you want to combine with
• ON table_a.column = table_b.column: The join condition that defines how rows match between the two tables

Here is your first INNER JOIN:

SELECT customers.first_name,
       customers.last_name,
       orders.order_date,
       orders.total_amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Output:

first_name	last_name	order_date	total_amount
Alice	Johnson	2024-01-10	119.98
Bob	Martinez	2024-01-15	89.99
Alice	Johnson	2024-02-20	77.49
Carol	Singh	2024-03-05	129.99
David	Chen	2024-03-12	155.00
Eva	Brown	2024-03-20	34.99
Bob	Martinez	2024-04-01	63.00
Frank	Wilson	2024-04-10	199.98
Alice	Johnson	2024-04-15	89.99
Carol	Singh	2024-04-18	63.99
Bob	Martinez	2024-04-22	174.99
David	Chen	2024-04-25	45.00

Now you can see who placed each order. Alice Johnson appears three times because she has three orders. The database matched each order's customer_id to the corresponding customer's id and combined the columns from both tables.

Reading the ON Clause

The ON clause reads like a sentence: "Join these tables where the customer's id equals the order's customer_id." The column on each side of the = is what the database uses to find matching rows.

ON customers.id = orders.customer_id
-- "Match the primary key in customers with the foreign key in orders"

This almost always follows a primary key = foreign key pattern, which directly mirrors the relationships you defined in your schema.

How INNER JOIN Works: Step by Step

Understanding what happens behind the scenes makes JOINs intuitive rather than mysterious.

The Matching Process

When the database executes an INNER JOIN, it conceptually does the following:

1. Take every row from the left table (customers):

id	first_name	last_name
1	Alice	Johnson
2	Bob	Martinez
3	Carol	Singh
4	David	Chen
5	Eva	Brown
6	Frank	Wilson
7	Grace	Taylor

2. For each row, find all matching rows in the right table (orders) where customers.id = orders.customer_id:

For customer 1 (Alice), find all orders where customer_id = 1:

• Order 1 (2024-01-10, $119.98) ✅ Match
• Order 3 (2024-02-20, $77.49) ✅ Match
• Order 9 (2024-04-15, $89.99) ✅ Match

For customer 7 (Grace), find all orders where customer_id = 7:

• No orders found ❌ No match

3. Combine the columns from both tables for each match. Discard rows with no match.

Alice produces 3 result rows (one per order). Grace produces 0 result rows because she has no orders. This is the defining behavior of INNER JOIN: only matching rows appear in the results.

The Venn Diagram Model

The most common way to visualize an INNER JOIN is as the intersection of two sets:

    ┌───────────────┐     ┌───────────────┐
    │               │     │               │
    │   customers   │     │    orders     │
    │   without  ┌──┼─────┼──┐  without   │
    │   orders   │  │     │  │  matching  │
    │            │ INNER  │  │  customers │
    │   (Grace)  │ JOIN   │  │            │
    │            │ result │  │            │
    │            └──┼─────┼──┘            │
    │               │     │               │
    └───────────────┘     └───────────────┘

• Left circle only (customers without orders): Grace. Excluded by INNER JOIN.
• Intersection (customers with orders): Alice, Bob, Carol, David, Eva, Frank. Included in results.
• Right circle only (orders without matching customers): Not possible in our database because of the foreign key constraint, but conceptually this area would also be excluded.

The INNER JOIN returns only the overlap, the rows that exist in both tables.

Key Characteristic of INNER JOIN

INNER JOIN is exclusive. If a row from either table has no match in the other table, it disappears from the results entirely. This is both its strength (clean, matched data) and its limitation (you lose unmatched rows). When you need to include unmatched rows, you will use LEFT JOIN (covered in the next guide).

Table Aliases: Making JOINs Readable

Writing customers.first_name and orders.order_date every time is verbose. Table aliases provide short abbreviations that make JOIN queries much cleaner.

Without Aliases (Verbose)

SELECT customers.first_name,
       customers.last_name,
       orders.order_date,
       orders.total_amount,
       orders.status
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
ORDER BY orders.order_date DESC;

With Aliases (Clean)

SELECT c.first_name,
       c.last_name,
       o.order_date,
       o.total_amount,
       o.status
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
ORDER BY o.order_date DESC;

Both queries produce identical results. The second is far easier to read and write.

Output:

first_name	last_name	order_date	total_amount	status
David	Chen	2024-04-25	45.00	pending
Bob	Martinez	2024-04-22	174.99	shipped
Carol	Singh	2024-04-18	63.99	completed
Alice	Johnson	2024-04-15	89.99	completed
Frank	Wilson	2024-04-10	199.98	pending
Bob	Martinez	2024-04-01	63.00	pending
Eva	Brown	2024-03-20	34.99	pending
David	Chen	2024-03-12	155.00	shipped
Carol	Singh	2024-03-05	129.99	shipped
Alice	Johnson	2024-02-20	77.49	completed
Bob	Martinez	2024-01-15	89.99	completed
Alice	Johnson	2024-01-10	119.98	completed

Alias Conventions

Choose short, meaningful abbreviations:

Table	Common Alias	Why
customers	c	First letter
orders	o	First letter
products	p	First letter
categories	cat	Avoids conflict with c for customers
order_items	oi	Initials
reviews	r	First letter

When Aliases Become Required

Table aliases are optional for single-table queries but become practically necessary with JOINs. When two tables have columns with the same name, you must qualify the column with the table name or alias to avoid ambiguity.

-- Error: Both tables have a column called 'id' and 'name'
SELECT id, name
FROM products
INNER JOIN categories ON products.category_id = categories.id;
-- ERROR: column reference "id" is ambiguous

-- Fixed: Use aliases to specify which table's columns you mean
SELECT p.id AS product_id, p.name AS product_name, cat.name AS category_name
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id;

Practical JOIN Examples

Let us work through the most common JOIN patterns you will encounter in real applications.

Products with Their Category Names

SELECT p.name AS product,
       p.price,
       cat.name AS category
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
ORDER BY cat.name, p.name;

Output:

product	price	category
SQL for Beginners	34.99	Books
Data Science Handbook	42.50	Books
Bluetooth Speaker	65.00	Electronics
Mechanical Keyboard	89.99	Electronics
USB-C Hub	45.00	Electronics
Wireless Mouse	29.99	Electronics
Coffee Maker Pro	129.99	Home & Kitchen
Running Shoes X1	110.00	Sports
Stainless Water Bottle	24.99	Sports
Yoga Mat Premium	38.00	Sports

The raw category_id numbers are replaced with readable category names. This is one of the most common reasons to use JOINs: replacing IDs with human-readable values from related tables.

Orders with Customer Information

SELECT o.id AS order_id,
       c.first_name || ' ' || c.last_name AS customer,
       c.city,
       o.order_date,
       o.total_amount,
       o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
ORDER BY o.order_date DESC
LIMIT 5;

Output:

order_id	customer	city	order_date	total_amount	status
12	David Chen	New York	2024-04-25	45.00	pending
11	Bob Martinez	Los Angeles	2024-04-22	174.99	shipped
10	Carol Singh	Chicago	2024-04-18	63.99	completed
9	Alice Johnson	New York	2024-04-15	89.99	completed
8	Frank Wilson	Chicago	2024-04-10	199.98	pending

Customer Order Summary

Combine a JOIN with GROUP BY to summarize orders per customer:

SELECT c.first_name || ' ' || c.last_name AS customer,
       c.email,
       COUNT(o.id) AS order_count,
       ROUND(SUM(o.total_amount), 2) AS total_spent,
       ROUND(AVG(o.total_amount), 2) AS avg_order_value
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.email
ORDER BY total_spent DESC;

Output:

customer	email	order_count	total_spent	avg_order_value
Bob Martinez	bob@email.com	3	327.98	109.33
Alice Johnson	alice@email.com	3	287.46	95.82
David Chen	david@email.com	2	200.00	100.00
Frank Wilson	frank@email.com	1	199.98	199.98
Carol Singh	carol@email.com	2	193.98	96.99
Eva Brown	eva@email.com	1	34.99	34.99

Notice that Grace Taylor (customer 7) is missing from these results. She has no orders, so the INNER JOIN finds no matches and excludes her. This is expected behavior for INNER JOIN.

Products with Average Ratings

SELECT p.name AS product,
       p.price,
       COUNT(r.id) AS review_count,
       ROUND(AVG(r.rating), 2) AS avg_rating
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, p.price
ORDER BY avg_rating DESC, review_count DESC;

Output:

product	price	review_count	avg_rating
Wireless Mouse	29.99	3	4.67
Running Shoes X1	110.00	3	4.67
USB-C Hub	45.00	2	4.50
SQL for Beginners	34.99	2	4.50
Coffee Maker Pro	129.99	2	4.50
Yoga Mat Premium	38.00	2	4.50
Stainless Water Bottle	24.99	3	4.33
Mechanical Keyboard	89.99	3	4.00
Data Science Handbook	42.50	1	3.00

The Bluetooth Speaker is missing because it has no reviews. INNER JOIN excludes products with no matching reviews.

Joining More Than Two Tables

Real queries frequently join three, four, or even more tables in a single statement. Each additional JOIN connects through another foreign key relationship.

Three Tables: Orders with Customer Names and Product Details

SELECT c.first_name || ' ' || c.last_name AS customer,
       o.order_date,
       p.name AS product,
       oi.quantity,
       oi.unit_price
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.id = 1;

Output:

customer	order_date	product	quantity	unit_price
Alice Johnson	2024-01-10	Wireless Mouse	2	29.99
Alice Johnson	2024-01-10	USB-C Hub	1	45.00

This query traverses four tables: customers → orders → order_items → products, following the foreign key chain. Each JOIN adds one more hop in the relationship graph.

Four Tables: Complete Order Details with Categories

SELECT c.first_name || ' ' || c.last_name AS customer,
       o.order_date,
       p.name AS product,
       cat.name AS category,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS line_total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories cat ON p.category_id = cat.id
ORDER BY o.order_date, customer, product;

Output:

customer	order_date	product	category	quantity	unit_price	line_total
Alice Johnson	2024-01-10	USB-C Hub	Electronics	1	45	45
Alice Johnson	2024-01-10	Wireless Mouse	Electronics	2	29.99	59.98
Bob Martinez	2024-01-15	Mechanical Keyboard	Electronics	1	89.99	89.99
Alice Johnson	2024-02-20	SQL for Beginners	Books	1	34.99	34.99
Alice Johnson	2024-02-20	Stainless Water Bottle	Sports	1	24.99	24.99
Carol Singh	2024-03-05	Coffee Maker Pro	Home & Kitchen	1	129.99	129.99
David Chen	2024-03-12	Running Shoes X1	Sports	1	110	110
David Chen	2024-03-12	USB-C Hub	Electronics	1	45	45
Eva Brown	2024-03-20	SQL for Beginners	Books	1	34.99	34.99
Bob Martinez	2024-04-01	Stainless Water Bottle	Sports	1	24.99	24.99
Bob Martinez	2024-04-01	Yoga Mat Premium	Sports	1	38	38
Frank Wilson	2024-04-10	Mechanical Keyboard	Electronics	1	89.99	89.99
Frank Wilson	2024-04-10	Running Shoes X1	Sports	1	110	110
Alice Johnson	2024-04-15	Mechanical Keyboard	Electronics	1	89.99	89.99
Carol Singh	2024-04-18	Stainless Water Bottle	Sports	1	24.99	24.99
Carol Singh	2024-04-18	Yoga Mat Premium	Sports	1	38	38
Bob Martinez	2024-04-22	Mechanical Keyboard	Electronics	1	89.99	89.99
Bob Martinez	2024-04-22	Running Shoes X1	Sports	1	110	110
David Chen	2024-04-25	USB-C Hub	Electronics	1	45	45

Five tables, four JOINs, one query. This is the kind of query that powers a complete order history page.

Reading Multi-Table JOINs

When reading a multi-table JOIN, follow the chain of ON conditions:

FROM customers c                                     -- Start here
INNER JOIN orders o ON c.id = o.customer_id          -- customers → orders
INNER JOIN order_items oi ON o.id = oi.order_id      -- orders → order_items
INNER JOIN products p ON oi.product_id = p.id        -- order_items → products
INNER JOIN categories cat ON p.category_id = cat.id  -- products → categories

Each ON clause connects the new table to one that has already been joined. Think of it as building a path through the ER diagram, one relationship at a time.

Order of JOINs

The order in which you write the JOIN clauses does not affect the results (the database optimizer rearranges them for efficiency). However, writing them in a logical sequence that follows the relationship chain makes your query much easier to read.

-- Logical order: Follow the relationship chain
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id

-- Also valid but harder to follow
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
JOIN customers c ON o.customer_id = c.id

Both return the same results. The first reads as a natural narrative: "Start with customers, find their orders, look at the items in each order, and get the product details."

INNER JOIN with WHERE, GROUP BY, and Other Clauses

JOINs integrate seamlessly with every other SQL clause you have learned.

JOIN + WHERE: Filtered Results

-- Find all Electronics products that have been ordered
SELECT DISTINCT p.name AS product, p.price
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
INNER JOIN order_items oi ON p.id = oi.product_id
WHERE cat.name = 'Electronics'
ORDER BY p.price DESC;

Output:

product	price
Mechanical Keyboard	89.99
USB-C Hub	45.00
Wireless Mouse	29.99

The Bluetooth Speaker is in Electronics but has never been ordered, so it does not appear (no matching rows in order_items).

JOIN + GROUP BY: Aggregated Results

-- Revenue per category
SELECT cat.name AS category,
       COUNT(DISTINCT o.id) AS order_count,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_revenue
FROM categories cat
INNER JOIN products p ON cat.id = p.category_id
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
GROUP BY cat.id, cat.name
ORDER BY total_revenue DESC;

Output:

category	order_count	total_revenue
Electronics	7	554.94
Sports	6	480.97
Home & Kitchen	1	129.99
Books	2	69.98

JOIN + GROUP BY + HAVING: Filtered Groups

-- Customers who have spent more than $200
SELECT c.first_name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS order_count,
       ROUND(SUM(o.total_amount), 2) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 200
ORDER BY total_spent DESC;

Output:

customer	order_count	total_spent
Bob Martinez	3	327.98
Alice Johnson	3	287.46

JOIN + ORDER BY + LIMIT: Top N Results

-- Top 3 best-reviewed products
SELECT p.name AS product,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       COUNT(r.id) AS review_count
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING COUNT(r.id) >= 2
ORDER BY avg_rating DESC, review_count DESC
LIMIT 3;

Output:

product	avg_rating	review_count
Wireless Mouse	4.67	3
Running Shoes X1	4.67	3
Coffee Maker Pro	4.50	2

JOIN vs INNER JOIN: Is There a Difference?

You will often see JOIN used without the INNER keyword:

-- These two queries are identical
SELECT c.first_name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

SELECT c.first_name, o.order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id;

JOIN and INNER JOIN are the same thing. The INNER keyword is optional and defaults to INNER when omitted. Both produce identical results.

Most developers use JOIN without INNER in everyday queries because it is shorter. Some prefer to write INNER JOIN explicitly when the query also uses LEFT JOIN or RIGHT JOIN, making the join type immediately clear throughout the query.

-- Mixing join types: explicit INNER helps readability
SELECT c.first_name, o.order_date, r.rating
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id;

Common INNER JOIN Mistakes

Mistake 1: Forgetting the ON Clause

-- Wrong: No ON clause creates a cartesian product
SELECT c.first_name, o.order_date
FROM customers c
INNER JOIN orders o;
-- ERROR (in most databases) or returns every possible combination
-- 7 customers × 12 orders = 84 rows of nonsense

Without ON, the database does not know how to match rows. Some databases raise an error; others produce a cartesian product (every row paired with every other row), which is almost never what you want.

Fix: Always include the ON clause with the correct matching condition:

SELECT c.first_name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Mistake 2: Wrong Join Condition

-- Wrong: Joining on the wrong columns
SELECT c.first_name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.id;
-- This matches customer id 1 with order id 1, customer id 2 with order id 2, etc.
-- Completely wrong relationships!

This accidentally matches customer IDs with order IDs, which are unrelated values that happen to share the same column name (id). The join condition must follow the foreign key relationship.

Fix: Join on the foreign key column:

SELECT c.first_name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- This correctly matches each order to its customer

How to Identify the Correct Join Condition

Always ask: "Which column in table B references which column in table A?" The answer is the foreign key relationship.

-- orders.customer_id references customers.id
ON c.id = o.customer_id    -- ✅ Correct

-- order_items.order_id references orders.id
ON o.id = oi.order_id      -- ✅ Correct

-- order_items.product_id references products.id
ON p.id = oi.product_id    -- ✅ Correct

-- products.category_id references categories.id
ON cat.id = p.category_id  -- ✅ Correct

If you are unsure about the relationship, check the table definitions or ER diagram.

Mistake 3: Ambiguous Column Names

-- Wrong: Both tables have a column called 'id'
SELECT id, first_name, order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- ERROR: column reference "id" is ambiguous

Fix: Always prefix column names with table aliases when joining:

SELECT c.id AS customer_id, c.first_name, o.id AS order_id, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Mistake 4: Unexpected Row Multiplication

When you join a table with a one-to-many relationship, rows from the "one" side get duplicated for each match on the "many" side. This is correct behavior, but it surprises beginners:

-- Alice has 3 orders, so her name appears 3 times
SELECT c.first_name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.first_name = 'Alice';

Output:

first_name	order_date
Alice	2024-01-10
Alice	2024-02-20
Alice	2024-04-15

This is not a bug. Each row represents a valid customer-order pair. If you want each customer to appear once with a summary, use GROUP BY:

SELECT c.first_name,
       COUNT(o.id) AS order_count,
       MIN(o.order_date) AS first_order,
       MAX(o.order_date) AS last_order
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.first_name = 'Alice'
GROUP BY c.id, c.first_name;

Output:

first_name	order_count	first_order	last_order
Alice	3	2024-01-10	2024-04-15

Real-World INNER JOIN Patterns

Pattern 1: Order Detail Page

The query that powers an order detail page in an e-commerce application:

SELECT o.id AS order_id,
       o.order_date,
       o.status,
       c.first_name || ' ' || c.last_name AS customer,
       c.email,
       p.name AS product,
       cat.name AS category,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories cat ON p.category_id = cat.id
WHERE o.id = 5
ORDER BY p.name;

Output:

order_id	order_date	status	customer	email	product	category	quantity	unit_price	line_total
5	2024-03-12	shipped	David Chen	david@email.com	Running Shoes X1	Sports	1	110.00	110.00
5	2024-03-12	shipped	David Chen	david@email.com	USB-C Hub	Electronics	1	45.00	45.00

Pattern 2: Product Catalog with Enriched Data

SELECT p.name AS product,
       cat.name AS category,
       p.price,
       p.stock_quantity AS stock,
       COALESCE(rev.review_count, 0) AS reviews,
       COALESCE(rev.avg_rating, 0) AS rating
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
LEFT JOIN (
    SELECT product_id,
           COUNT(*) AS review_count,
           ROUND(AVG(rating), 1) AS avg_rating
    FROM reviews
    GROUP BY product_id
) rev ON p.id = rev.product_id
WHERE p.is_available = true
ORDER BY cat.name, p.name;

Output:

product	category	price	stock	reviews	rating
Data Science Handbook	Books	42.50	30	1	3.0
SQL for Beginners	Books	34.99	50	2	4.5
Mechanical Keyboard	Electronics	89.99	75	3	4.0
USB-C Hub	Electronics	45.00	200	2	4.5
Wireless Mouse	Electronics	29.99	150	3	4.7
Coffee Maker Pro	Home & Kitchen	129.99	25	2	4.5
Running Shoes X1	Sports	110.00	60	3	4.7
Stainless Water Bottle	Sports	24.99	180	3	4.3
Yoga Mat Premium	Sports	38.00	100	2	4.5

Pattern 3: Sales by Category and Month

SELECT cat.name AS category,
       TO_CHAR(o.order_date, 'YYYY-MM') AS month,
       COUNT(DISTINCT o.id) AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM categories cat
INNER JOIN products p ON cat.id = p.category_id
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
GROUP BY cat.name, TO_CHAR(o.order_date, 'YYYY-MM')
ORDER BY category, month;

Output:

category	month	orders	revenue
Books	2024-02	1	34.99
Books	2024-03	1	34.99
Electronics	2024-01	2	194.97
Electronics	2024-03	1	45.00
Electronics	2024-04	3	314.96
Home & Kitchen	2024-03	1	129.99
Sports	2024-02	1	24.99
Sports	2024-03	1	110.00
Sports	2024-04	4	260.97

Practical Exercises

Exercise 1

Write a query that shows every product alongside its category name, sorted by category and then by price.

SELECT p.name AS product,
       cat.name AS category,
       p.price
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
ORDER BY cat.name, p.price;

Expected output:

product	category	price
SQL for Beginners	Books	34.99
Data Science Handbook	Books	42.50
Wireless Mouse	Electronics	29.99
USB-C Hub	Electronics	45.00
Bluetooth Speaker	Electronics	65.00
Mechanical Keyboard	Electronics	89.99
Coffee Maker Pro	Home & Kitchen	129.99
Stainless Water Bottle	Sports	24.99
Yoga Mat Premium	Sports	38.00
Running Shoes X1	Sports	110.00

Exercise 2

Find the 3 customers who have placed the most orders, showing their name and order count.

SELECT c.first_name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY order_count DESC
LIMIT 3;

Expected output:

customer	order_count
Alice Johnson	3
Bob Martinez	3
Carol Singh	2

Exercise 3

Find the total revenue generated by each product (based on order items), showing only products with revenue over $100.

SELECT p.name AS product,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
HAVING SUM(oi.quantity * oi.unit_price) > 100
ORDER BY total_revenue DESC;

Expected output:

product	total_revenue
Mechanical Keyboard	359.96
Running Shoes X1	330.00
USB-C Hub	135.00
Coffee Maker Pro	129.99

Exercise 4

Write a query that shows each order with the customer name, the number of items in the order, and the order total.

SELECT o.id AS order_id,
       c.first_name || ' ' || c.last_name AS customer,
       o.order_date,
       COUNT(oi.id) AS item_count,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS calculated_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.first_name, c.last_name, o.order_date
ORDER BY o.order_date DESC;

order_id	customer	order_date	item_count	calculated_total
12	David Chen	2024-04-25	1	45.00
11	Bob Martinez	2024-04-22	2	199.99
10	Carol Singh	2024-04-18	2	62.99
9	Alice Johnson	2024-04-15	1	89.99
8	Frank Wilson	2024-04-10	2	199.99
7	Bob Martinez	2024-04-01	2	62.99
6	Eva Brown	2024-03-20	1	34.99
5	David Chen	2024-03-12	2	155.00
4	Carol Singh	2024-03-05	1	129.99
3	Alice Johnson	2024-02-20	2	59.98
2	Bob Martinez	2024-01-15	1	89.99
1	Alice Johnson	2024-01-10	2	104.98

Exercise 5

Find all reviews along with the product name, customer name, and rating. Sort by rating descending.

SELECT p.name AS product,
       c.first_name || ' ' || c.last_name AS reviewer,
       r.rating,
       r.review_date
FROM reviews r
INNER JOIN products p ON r.product_id = p.id
INNER JOIN customers c ON r.customer_id = c.id
ORDER BY r.rating DESC, r.review_date DESC
LIMIT 10;

product	reviewer	rating	review_date
Running Shoes X1	Alice Johnson	5	2024-04-01
Stainless Water Bottle	Eva Brown	5	2024-03-20
USB-C Hub	Frank Wilson	5	2024-03-12
Coffee Maker Pro	Carol Singh	5	2024-03-10
Running Shoes X1	David Chen	5	2024-03-08
Yoga Mat Premium	Eva Brown	5	2024-02-28
Wireless Mouse	Carol Singh	5	2024-02-10
SQL for Beginners	Alice Johnson	5	2024-01-30
Mechanical Keyboard	Alice Johnson	5	2024-01-22
Wireless Mouse	Alice Johnson	5	2024-01-20

Key Takeaways

INNER JOIN is the most fundamental and most frequently used type of join. Here is what you should remember:

• INNER JOIN combines rows from two tables based on a matching condition and returns only rows that have matches in both tables
• Unmatched rows are excluded from both sides. If a customer has no orders, they disappear from the results
• The ON clause defines how rows match, almost always following a primary key = foreign key pattern
• JOIN without the INNER keyword is identical to INNER JOIN
• Table aliases (FROM customers c) are essential for readable, maintainable JOIN queries
• You can chain multiple JOINs to traverse relationships across many tables
• JOINs integrate with all other SQL clauses: WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
• Rows from the "one" side of a one-to-many relationship get duplicated for each match on the "many" side. This is correct behavior, not a bug.
• Always qualify column names with table aliases when joining tables that share column names
• The join condition must follow the correct foreign key relationship. Joining on the wrong columns produces incorrect, misleading results
• When you need to include unmatched rows (like customers with no orders), use LEFT JOIN instead, which is the topic of the next guide

INNER JOIN is the workhorse of SQL. The majority of production queries involve at least one join, and understanding how matching works, how to chain multiple joins, and how to combine them with aggregation and filtering will serve you in virtually every SQL task you encounter.