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:

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:

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.

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):

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.

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:

Alias Conventions

Choose short, meaningful abbreviations:

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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

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:

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

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:

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:

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:

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:

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:

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:

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:

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:

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;

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;

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.