JOINs — Combining Tables

Real-world data doesn't live in a single table. Customer names are in one table, their orders in another, and product details in a third. JOINs let you combine them. Understanding JOINs is the single biggest skill jump between "can write basic SQL" and "can do real analytics."

Learning Objectives

• Understand the relational model — why data is split across tables
• Write INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
• Join more than two tables in one query
• Diagnose and fix common join mistakes (duplicates, wrong join column)

---

Why Data is Spread Across Tables

Consider an e-commerce database. Storing customer name in every order row would: - Waste storage (1000 orders for one customer = 1000 copies of their name) - Create update anomalies (change the name in one row, 999 other rows go stale)

Instead, the database stores: - customers — one row per customer, with customer details - orders — one row per order, with customer_id as a foreign key linking back

A JOIN reconnects them at query time.

---

INNER JOIN — Only Matching Rows

Returns rows where the join condition is true in both tables.

SELECT
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    o.quantity * o.unit_price   AS order_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

Sample output:

order_id	order_date	first_name	last_name	order_total
O008	2024-01-18	Arjun	Mehta	389.00
O007	2024-01-15	Sarah	Johnson	249.95
O006	2024-01-14	James	O'Brien	152.98

Table aliases make JOINs readable

FROM orders o gives the orders table the alias o. Then o.order_id, o.order_date etc. are unambiguous. Without aliases, if both tables have a column called customer_id, SQL doesn't know which one you mean.

---

LEFT JOIN — All Rows from the Left Table

Returns all rows from the left table (before JOIN), plus matching rows from the right. If no match exists in the right table, the right-side columns are NULL.

Business use case: "Find all customers, including those who have never placed an order."

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

Sample output:

customer_id	first_name	last_name	order_id	order_date
C001	Priya	Sharma	O001	2024-01-05
C001	Priya	Sharma	O004	2024-01-10
C002	Arjun	Mehta	O003	2024-01-09
C002	Arjun	Mehta	O008	2024-01-18
C004	Liu	Wei	O005	2024-01-11
C006	Neha	Patel	NULL	NULL

C006 has never placed an order — they appear with NULLs on the right side.

Finding rows with no match — the NULL trick

-- Customers who have never ordered
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This pattern (LEFT JOIN + WHERE right-side IS NULL) is one of the most useful in analytics.

---

RIGHT JOIN — All Rows from the Right Table

Mirror image of LEFT JOIN. Returns all rows from the right table, NULLs for left-side where no match.

In practice, RIGHT JOIN is almost always rewritten as a LEFT JOIN with the tables swapped — most teams avoid RIGHT JOIN because it's harder to read.

-- These two queries return identical results
SELECT c.customer_id, o.order_id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

-- Easier to read as LEFT JOIN:
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

---

FULL OUTER JOIN — All Rows from Both Tables

Returns all rows from both tables. Where no match exists in either direction, NULLs fill the gaps.

MySQL note: MySQL does not support FULL OUTER JOIN natively. Simulate it with UNION:

-- PostgreSQL / SQL Server
SELECT c.customer_id, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- MySQL equivalent
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

---

Joining Three Tables

SELECT
    o.order_id,
    o.order_date,
    c.first_name || ' ' || c.last_name  AS customer_name,
    p.product_name,
    p.category,
    o.quantity,
    o.unit_price,
    o.quantity * o.unit_price           AS order_total
FROM orders o
INNER JOIN customers c  ON o.customer_id = c.customer_id
INNER JOIN products p   ON o.product_id  = p.product_id
WHERE o.status = 'completed'
ORDER BY o.order_date DESC;

Sample output:

order_id	order_date	customer_name	product_name	category	quantity	unit_price	order_total
O006	2024-01-14	James O'Brien	Coffee Maker	Kitchen	2	89.99	179.98
O004	2024-01-10	Priya Sharma	Yoga Mat	Sports	1	35.99	35.99
O002	2024-01-07	Sarah Johnson	Standing Desk	Furniture	1	389.00	389.00

---

CROSS JOIN — Every Combination

Produces the Cartesian product — every row in table A paired with every row in table B. Rarely used deliberately.

-- 5 customers × 5 products = 25 rows (a price matrix)
SELECT c.customer_id, p.product_name
FROM customers c
CROSS JOIN products p;

Accidental CROSS JOINs destroy performance

Forgetting the ON condition in a JOIN creates an implicit CROSS JOIN. On tables with 1M rows each, this produces 1 trillion rows. Always double-check your ON clause.

---

Common Join Mistakes

Joining on the wrong column

-- Wrong: joining customer_id to product_id — no rows will match
FROM orders o
JOIN products p ON o.customer_id = p.product_id;

-- Correct
JOIN products p ON o.product_id = p.product_id;

Duplicate rows from one-to-many relationships

If a customer has 5 orders and you JOIN customers to orders, that customer appears 5 times. This is correct behaviour — but if you then aggregate, you'll double-count customer attributes. Solution: aggregate the orders first (in a subquery or CTE), then join.

For senior analysts

Always check row counts before and after a JOIN: SELECT COUNT(*) FROM orders vs SELECT COUNT(*) FROM orders JOIN customers .... If the count increases, you have a one-to-many relationship creating duplicates. If it decreases, you have unmatched rows being dropped by INNER JOIN.

---

Practice Exercises

Warm-up 1. Write an INNER JOIN between orders and customers to get: order_id, order_date, customer first_name, last_name. 2. Write a LEFT JOIN to list all customers and their orders. Show customers with no orders as well. 3. Find all products that have never been ordered. (Hint: LEFT JOIN + IS NULL.)

Main 4. Join all three tables to create a full order summary: order_id, customer full name, product_name, category, order_total. 5. Find the top 5 customers by total order value (completed orders only). Show customer_id, full_name, total_revenue. 6. For each product category, how many orders have been placed? Show category, order count, and total quantity sold.

Stretch 7. Find customers who ordered in January 2024 but have NOT placed any order since then (after January 31). 8. Write a query that shows each product alongside the total revenue it generated and what percentage that is of overall total revenue.

---

Interview Questions

[Beginner] What is a JOIN in SQL and why is it needed?

[Beginner] What is the difference between INNER JOIN and LEFT JOIN?

[Mid-level] You have a customers table (500 rows) and an orders table (50,000 rows). You run an INNER JOIN and get 75,000 rows. What does this mean, and is it a problem?

[Mid-level] Write a query to find customers who have never placed an order.

[Mid-level] What is a self-join? Give a business example where you would use one.

[Senior] A JOIN query between a 10M-row orders table and a 1M-row customers table is taking 2 minutes. How would you diagnose and fix it?

---

← Agenda · Next: GROUP BY and HAVING →