INNER JOIN - Combining Related Data

Introduction

You've learned about relationships between tables. Now it's time to query those relationships! The INNER JOIN combines rows from two or more tables based on a related column, returning only rows where matches exist in both tables.

INNER JOIN is the most commonly used JOIN type and the foundation for understanding all other JOINs.

The JOIN Problem

Querying One Table at a Time

-- Get customer info
SELECT * FROM customers WHERE id = 1;

-- Get their orders (separate query)
SELECT * FROM orders WHERE customer_id = 1;

Problem: Data is separated across multiple queries. What if you want customer name AND order details together?

The Solution: INNER JOIN

Combine data from both tables in a single query!

SELECT
    customers.name,
    orders.id AS order_id,
    orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Result:

    name      | order_id | total
--------------+----------+--------
 Alice Smith  |     1    | 100.00
 Alice Smith  |     2    |  75.50
 Bob Jones    |     3    | 200.00

INNER JOIN Syntax

Basic Syntax

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Breakdown

• FROM table1: The "left" table (starting point)
• INNER JOIN table2: The "right" table to join
• ON condition: How to match rows between tables

Example Setup

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE,
    total DECIMAL(10,2)
);

-- Insert data
INSERT INTO customers (id, name, email) VALUES
    (1, 'Alice Smith', 'alice@example.com'),
    (2, 'Bob Jones', 'bob@example.com'),
    (3, 'Carol White', 'carol@example.com');

INSERT INTO orders (id, customer_id, order_date, total) VALUES
    (1, 1, '2024-01-15', 100.00),
    (2, 1, '2024-01-20', 75.50),
    (3, 2, '2024-01-18', 200.00);

Your First INNER JOIN

SELECT
    customers.name,
    customers.email,
    orders.id AS order_id,
    orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Result:

    name      |        email         | order_id | total
--------------+----------------------+----------+--------
 Alice Smith  | alice@example.com    |     1    | 100.00
 Alice Smith  | alice@example.com    |     2    |  75.50
 Bob Jones    | bob@example.com      |     3    | 200.00

Notice:

• Alice appears twice (she has 2 orders)
• Carol is missing (she has 0 orders)
• INNER JOIN only returns matches!

Table Aliases

Using AS for Readability

SELECT
    c.name,
    o.total
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;

AS is Optional

SELECT
    c.name,
    o.total
FROM customers c  -- No AS needed
INNER JOIN orders o ON c.id = o.customer_id;

Benefits:

• Less typing
• More readable
• Required when joining a table to itself

Selecting Specific Columns

Qualify Column Names

-- If column exists in both tables, specify which one
SELECT
    customers.id,        -- From customers table
    orders.id,           -- From orders table (different!)
    customers.name,
    orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Using Aliases

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

Multiple JOINs

Join more than two tables:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER
);

-- Insert data
INSERT INTO products VALUES (101, 'Laptop', 999.99), (102, 'Mouse', 25.99);
INSERT INTO order_items VALUES
    (1, 1, 101, 1),  -- Order 1: 1 Laptop
    (2, 1, 102, 2);  -- Order 1: 2 Mice

-- JOIN three tables
SELECT
    c.name AS customer,
    o.id AS order_id,
    p.name AS product,
    oi.quantity,
    p.price * oi.quantity AS item_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;

Result:

  customer   | order_id | product | quantity | item_total
-------------+----------+---------+----------+------------
 Alice Smith |     1    | Laptop  |    1     |    999.99
 Alice Smith |     1    | Mouse   |    2     |     51.98

WHERE with INNER JOIN

Filter results after joining:

SELECT
    c.name,
    o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;  -- Only orders over $100

Order of execution:

1. JOIN tables
2. Filter with WHERE
3. Return results

Aggregating with JOINs

Count Orders per Customer

SELECT
    c.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Result:

    name      | order_count | total_spent
--------------+-------------+-------------
 Alice Smith  |      2      |   175.50
 Bob Jones    |      1      |   200.00

Notice: Carol is missing (she has no orders, so no match in INNER JOIN).

JOIN Conditions Beyond Equality

Range Conditions

SELECT *
FROM sales s
INNER JOIN targets t
    ON s.region = t.region
    AND s.amount >= t.min_amount
    AND s.amount <= t.max_amount;

Multiple Columns

SELECT *
FROM employees e1
INNER JOIN employees e2
    ON e1.department = e2.department
    AND e1.hire_date = e2.hire_date
WHERE e1.id < e2.id;  -- Avoid duplicate pairs

Common Patterns

Pattern 1: Customer Orders Summary

SELECT
    c.name,
    c.email,
    COUNT(o.id) AS total_orders,
    SUM(o.total) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC;

Pattern 2: Product Sales Report

SELECT
    p.name AS product,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * p.price) AS revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY revenue DESC;

Pattern 3: Filtering by Related Data

-- Find customers who ordered laptops
SELECT DISTINCT c.name
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 p.name = 'Laptop';

INNER JOIN vs WHERE (Old Syntax)

Old-Style (Avoid)

-- Implicit join (WHERE clause)
SELECT c.name, o.total
FROM customers c, orders o
WHERE c.id = o.customer_id;

Modern Style (Recommended)

-- Explicit INNER JOIN
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Why modern is better:

• Clearer intent
• Separates join logic from filter logic
• Easier to read and maintain
• Supports all JOIN types (LEFT, RIGHT, FULL)

Performance Tips

Index Foreign Keys

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Makes JOINs much faster!

Join on Indexed Columns

-- ✅ Fast: Primary key to foreign key
SELECT *
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- ❌ Slower: Non-indexed columns
SELECT *
FROM customers c
INNER JOIN orders o ON c.email = o.customer_email;

Filter Before Joining (When Possible)

-- ✅ Better: Filter first, then join
SELECT c.name, o.total
FROM customers c
INNER JOIN (
    SELECT * FROM orders WHERE order_date >= '2024-01-01'
) o ON c.id = o.customer_id;

Common Mistakes

Mistake 1: Missing ON Clause

-- ❌ Error: Missing ON
SELECT * FROM customers
INNER JOIN orders;

Mistake 2: Cartesian Product

-- ❌ Wrong: Every customer matched with every order
SELECT * FROM customers c, orders o;
-- Returns 3 customers × 3 orders = 9 rows!

-- ✅ Correct: Proper JOIN condition
SELECT * FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Mistake 3: Ambiguous Column Names

-- ❌ Error: Both tables have "id"
SELECT id FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

-- ✅ Correct: Specify table
SELECT customers.id, orders.id
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Practice Exercises

Exercise 1

Find all orders with customer names and email addresses.

SELECT
    c.name,
    c.email,
    o.id AS order_id,
    o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Exercise 2

Find customers who have placed more than 1 order.

SELECT
    c.name,
    COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 1;

Exercise 3

Find the total quantity of each product sold.

SELECT
    p.name,
    SUM(oi.quantity) AS total_quantity
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

Key Takeaways

• ✅ INNER JOIN returns only matching rows from both tables
• ✅ ON clause specifies how to match rows
• ✅ Table aliases (c, o, p) make queries more readable
• ✅ Multiple JOINs combine three or more tables
• ✅ Customers without orders are excluded (use LEFT JOIN to include them)
• ✅ Index foreign keys for faster JOINs
• ✅ Modern JOIN syntax is clearer than old WHERE-based joins

Next Steps

INNER JOIN is powerful, but what if you want to include rows that don't have matches? In the next lesson, we'll learn LEFT JOIN and RIGHT JOIN—how to include unmatched rows from one side of the join.

Keep building your JOIN skills!