JOINs — Combining Tables | PostgreSQL Mastery

📦 Our Practice Tables

JOINs are all about combining data from two or more tables. So we need at least two tables to play with! We'll create a customers table and an orders table, and use them throughout the entire lesson.

🧒 ELI5 — Why Two Tables?

Imagine a school. One notebook has the student roster (names, ages, classes). Another notebook has the library checkout log (which student borrowed which book). The checkout log only writes down the student's ID number, not their whole name. To find out "Who borrowed Harry Potter?" you have to look at BOTH notebooks — match the ID from the checkout log to the ID in the roster. That's exactly what a JOIN does! It matches rows across two tables using a shared column.

Run this SQL to create and populate both tables:

-- ==========================================
-- TABLE 1: customers
-- ==========================================
CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100),
    city  VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO customers (name, city, email) VALUES
    ('Alice Johnson',  'New York',      'alice@example.com'),
    ('Bob Smith',      'Los Angeles',   'bob@example.com'),
    ('Charlie Brown',  'Chicago',       'charlie@example.com'),
    ('Diana Prince',   'Houston',       'diana@example.com'),
    ('Eve Williams',   'Phoenix',       'eve@example.com'),
    ('Frank Miller',   'New York',      'frank@example.com'),
    ('Grace Lee',      'San Francisco', 'grace@example.com'),
    ('Hank Davis',     'Seattle',       'hank@example.com');

-- ==========================================
-- TABLE 2: orders
-- ==========================================
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    product     VARCHAR(100),
    amount      DECIMAL(10,2),
    order_date  DATE
);

INSERT INTO orders (customer_id, product, amount, order_date) VALUES
    (1, 'Laptop',          1200.00, '2024-01-15'),
    (1, 'Mouse',           25.99,   '2024-01-15'),
    (2, 'Headphones',      79.99,   '2024-02-03'),
    (3, 'Keyboard',        149.99,  '2024-02-10'),
    (3, 'Monitor',         399.99,  '2024-02-10'),
    (3, 'USB Hub',         34.99,   '2024-03-01'),
    (1, 'Webcam',          89.99,   '2024-03-05'),
    (4, 'Tablet',          499.99,  '2024-03-12'),
    (5, 'Phone Case',      19.99,   '2024-03-20'),
    (5, 'Screen Protector', 9.99,    '2024-03-20'),
    (2, 'Laptop Stand',    59.99,   '2024-04-01'),
    (4, 'Stylus Pen',      29.99,   '2024-04-05');

Here's what our customers table looks like:

id	name	city	email
1	Alice Johnson	New York	alice@example.com
2	Bob Smith	Los Angeles	bob@example.com
3	Charlie Brown	Chicago	charlie@example.com
4	Diana Prince	Houston	diana@example.com
5	Eve Williams	Phoenix	eve@example.com
6	Frank Miller	New York	frank@example.com
7	Grace Lee	San Francisco	grace@example.com
8	Hank Davis	Seattle	hank@example.com

And here's the orders table:

id	customer_id	product	amount	order_date
1	1	Laptop	1200.00	2024-01-15
2	1	Mouse	25.99	2024-01-15
3	2	Headphones	79.99	2024-02-03
4	3	Keyboard	149.99	2024-02-10
5	3	Monitor	399.99	2024-02-10
6	3	USB Hub	34.99	2024-03-01
7	1	Webcam	89.99	2024-03-05
8	4	Tablet	499.99	2024-03-12
9	5	Phone Case	19.99	2024-03-20
10	5	Screen Protector	9.99	2024-03-20
11	2	Laptop Stand	59.99	2024-04-01
12	4	Stylus Pen	29.99	2024-04-05

💡 Pro Tip — Notice the Key Relationship!

The customer_id column in the orders table points to the id column in the customers table. This is called a foreign key. Also notice: customers 6, 7, and 8 (Frank, Grace, Hank) have no orders at all. We did this on purpose — these "orphan" customers are critical for understanding how different JOIN types behave!

1️⃣ Why JOINs?

🧒 ELI5 — The Pizza Order Problem

You work at a pizza shop. You have two lists taped to the wall:

List A (Orders): "Order #101 → Customer #7, Pepperoni Pizza." "Order #102 → Customer #3, Margherita Pizza."

List B (Customers): "Customer #7 = Sarah." "Customer #3 = Mike."

A delivery driver walks in and asks: "Who ordered the Pepperoni?" You can't answer from List A alone — it only says "Customer #7." You need to look up #7 on List B to find out it's Sarah. That's a JOIN! You're combining information from two separate lists using a matching ID.

In a relational database, data is split across multiple tables to avoid repetition. Instead of writing "Alice Johnson, New York, alice@example.com" on every single order, we just write her ID number (1). This saves space and keeps data consistent — if Alice moves to a new city, we update one row in the customers table instead of hundreds of order rows.

But this means to get a complete picture, we need to combine the tables back together. That's where JOINs come in.

8

Customers in our table

12

Orders placed

3

Customers with NO orders

⚠️ Without JOINs, Relational Databases Are Useless!

If you can't combine tables, your data is locked in separate silos. You can see what was ordered but not who ordered it. You can see who your customers are but not what they bought. JOINs are the single most important concept in SQL. Master this and you can handle 90% of real-world database queries.

2️⃣ INNER JOIN — Only the Matches

🧒 ELI5 — The Buddy System

Your teacher says: "Everyone find a buddy! If you don't have a buddy, you sit out." INNER JOIN works the same way. It looks at both tables and says: "Only show me rows where there's a match in BOTH tables." Customers who never placed an order? They sit out. Orders that (somehow) don't have a valid customer? They sit out too. Only matched pairs make it into the result.

Think of a Venn diagram — INNER JOIN gives you only the overlapping part in the middle.

The Syntax

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

The ON clause tells PostgreSQL how to match rows. It's like saying: "The customer_id in the orders table should match the id in the customers table."

Example 1: Who Ordered What?

SELECT c.name, o.product, o.amount, o.order_date
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id
ORDER BY c.name, o.order_date;

Result:

name	product	amount	order_date
Alice Johnson	Laptop	1200.00	2024-01-15
Alice Johnson	Mouse	25.99	2024-01-15
Alice Johnson	Webcam	89.99	2024-03-05
Bob Smith	Headphones	79.99	2024-02-03
Bob Smith	Laptop Stand	59.99	2024-04-01
Charlie Brown	Keyboard	149.99	2024-02-10
Charlie Brown	Monitor	399.99	2024-02-10
Charlie Brown	USB Hub	34.99	2024-03-01
Diana Prince	Tablet	499.99	2024-03-12
Diana Prince	Stylus Pen	29.99	2024-04-05
Eve Williams	Phone Case	19.99	2024-03-20
Eve Williams	Screen Protector	9.99	2024-03-20

⚠️ Where Did Frank, Grace, and Hank Go?

Notice they're completely missing from the results! They exist in the customers table but have no matching rows in the orders table. INNER JOIN silently drops them. This is the #1 gotcha with INNER JOIN — you might lose data you didn't expect to lose. If you need to keep ALL customers (even those without orders), you need a LEFT JOIN instead.

Example 2: Total Spent Per Customer

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

Result:

name	total_orders	total_spent
Alice Johnson	3	1315.98
Charlie Brown	3	584.97
Diana Prince	2	529.98
Bob Smith	2	139.98
Eve Williams	2	29.98

💡 Pro Tip — Table Aliases Save Typing!

Notice how we wrote FROM customers c instead of FROM customers? The c is an alias — a short nickname for the table. Then instead of writing customers.name everywhere, we just write c.name. When you're joining multiple tables, aliases go from "nice to have" to "absolutely essential" for readability.

🌍 Real-Life Analogy

INNER JOIN is like a dance partner matchup. Only people who have a dance partner get on the floor. If you showed up alone, you sit on the sidelines — no matter how good a dancer you are. Both the lead and the follow must be present for a pair to appear in the results.

3️⃣ LEFT JOIN (LEFT OUTER JOIN) — Keep All Left Rows

🧒 ELI5 — The Class Photo

It's school photo day. The rule is: every student gets their photo taken, no exceptions. If a student brought a pet for "Pets & People Day," great — the pet is in the photo too! If a student didn't bring a pet, they still get their photo taken — just alone, with an empty spot where the pet would be. That empty spot is NULL.

LEFT JOIN means: "Keep EVERY row from the left table (customers), and if there's a match on the right table (orders), include it. If there's no match, fill in NULLs."

The Syntax

SELECT columns
FROM left_table
LEFT JOIN right_table
    ON left_table.column = right_table.column;

Example: All Customers + Their Orders (If Any)

SELECT c.name, c.city, o.product, o.amount
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
ORDER BY c.id, o.id;

Result:

name	city	product	amount
Alice Johnson	New York	Laptop	1200.00
Alice Johnson	New York	Mouse	25.99
Alice Johnson	New York	Webcam	89.99
Bob Smith	Los Angeles	Headphones	79.99
Bob Smith	Los Angeles	Laptop Stand	59.99
Charlie Brown	Chicago	Keyboard	149.99
Charlie Brown	Chicago	Monitor	399.99
Charlie Brown	Chicago	USB Hub	34.99
Diana Prince	Houston	Tablet	499.99
Diana Prince	Houston	Stylus Pen	29.99
Eve Williams	Phoenix	Phone Case	19.99
Eve Williams	Phoenix	Screen Protector	9.99
Frank Miller	New York	NULL	NULL
Grace Lee	San Francisco	NULL	NULL
Hank Davis	Seattle	NULL	NULL

Frank, Grace, and Hank are back! They appear in the results with NULL for product and amount because they have no orders. This is the magic of LEFT JOIN.

Killer Use Case: Find Customers Who NEVER Ordered

-- LEFT JOIN + WHERE ... IS NULL = "find unmatched rows"
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.id IS NULL;

Result:

name	email
Frank Miller	frank@example.com
Grace Lee	grace@example.com
Hank Davis	hank@example.com

🌍 Real-Life Analogy

This pattern (LEFT JOIN + IS NULL) is incredibly common in the real world. E-commerce companies use it to find "registered users who never purchased anything" so they can send them a discount code. SaaS companies use it to find "signed-up users who never activated their account." It's one of the most powerful patterns in SQL!

💡 Pro Tip — LEFT JOIN vs LEFT OUTER JOIN

LEFT JOIN and LEFT OUTER JOIN are exactly the same thing. The word "OUTER" is optional. Most people drop it for brevity. Same for RIGHT JOIN / RIGHT OUTER JOIN and FULL JOIN / FULL OUTER JOIN.

4️⃣ RIGHT JOIN — Keep All Right Rows

🧒 ELI5 — The Mirror Image

RIGHT JOIN is just LEFT JOIN flipped around. Instead of keeping all rows from the left table, it keeps all rows from the right table. It's like looking at a LEFT JOIN in a mirror.

Imagine the same school photo scenario, but now the rule is: "Every PET gets photographed, even if their owner didn't show up." The pets are the right table — they all appear. Owners who are there get in the photo; missing owners become NULL.

Example: All Orders + Customer Info (If Available)

SELECT c.name, o.product, o.amount
FROM customers c
RIGHT JOIN orders o
    ON c.id = o.customer_id
ORDER BY o.id;

Result: Since every order in our data has a valid customer_id, this produces the same 12 rows as INNER JOIN. RIGHT JOIN becomes different when the right table has rows that don't match the left table (for example, if an order had customer_id = 99 and no customer #99 existed).

⚠️ RIGHT JOIN Is Rarely Used in Practice

You can always rewrite a RIGHT JOIN as a LEFT JOIN by simply swapping the table order. Most SQL developers strongly prefer LEFT JOIN because it reads more naturally: "Start with the main table, then optionally attach related data." If you see a RIGHT JOIN in someone's code, you can mentally convert it: A RIGHT JOIN B is identical to B LEFT JOIN A.

-- These two queries produce IDENTICAL results:

-- Using RIGHT JOIN
SELECT c.name, o.product
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- Same thing as LEFT JOIN with tables swapped
SELECT c.name, o.product
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;

5️⃣ FULL OUTER JOIN — Everything From Both Sides

🧒 ELI5 — Merging Two Party Guest Lists

You and your friend are both throwing parties on the same day. You each have a guest list. Now you want to see everyone across both lists. Some people are on both lists (they were invited to both parties). Some are only on your list. Some are only on your friend's list. A FULL OUTER JOIN shows ALL of them — nobody gets left out.

For the people only on one list, the columns from the other list show up as NULL. It's the most inclusive JOIN type.

The Syntax

SELECT columns
FROM table_a
FULL OUTER JOIN table_b
    ON table_a.column = table_b.column;

Example: Full Picture of Customers and Orders

SELECT c.name, c.city, o.product, o.amount
FROM customers c
FULL OUTER JOIN orders o
    ON c.id = o.customer_id
ORDER BY c.id NULLS LAST, o.id NULLS LAST;

Result: All 12 matched rows (customers with orders) PLUS the 3 unmatched customers (Frank, Grace, Hank with NULL products). If we had "orphan" orders that didn't match any customer, they'd appear too with NULL names.

name	city	product	amount
Alice Johnson	New York	Laptop	1200.00
Alice Johnson	New York	Mouse	25.99
Alice Johnson	New York	Webcam	89.99
Bob Smith	Los Angeles	Headphones	79.99
Bob Smith	Los Angeles	Laptop Stand	59.99
Charlie Brown	Chicago	Keyboard	149.99
Charlie Brown	Chicago	Monitor	399.99
Charlie Brown	Chicago	USB Hub	34.99
Diana Prince	Houston	Tablet	499.99
Diana Prince	Houston	Stylus Pen	29.99
Eve Williams	Phoenix	Phone Case	19.99
Eve Williams	Phoenix	Screen Protector	9.99
Frank Miller	New York	NULL	NULL
Grace Lee	San Francisco	NULL	NULL
Hank Davis	Seattle	NULL	NULL

🌍 Real-Life Analogy

FULL OUTER JOIN is like a complete census. Imagine merging a "tax filers" list with a "property owners" list. Some people filed taxes AND own property (matched). Some filed taxes but don't own property (left only). Some own property but didn't file taxes (right only). A FULL OUTER JOIN gives you the complete picture of all citizens, regardless of which list they're on.

💡 Pro Tip — When to Use FULL OUTER JOIN

FULL OUTER JOIN is less common than INNER or LEFT JOIN, but it's invaluable for data reconciliation. For example: comparing two data sources to find discrepancies, merging old and new systems, or finding records that exist in one system but not the other.

6️⃣ CROSS JOIN — The Cartesian Product

🧒 ELI5 — Every T-Shirt × Every Color

Imagine you sell T-shirts in 3 sizes (S, M, L) and 4 colors (Red, Blue, Green, Black). You want to list every possible combination: S-Red, S-Blue, S-Green, S-Black, M-Red, M-Blue... and so on. That's 3 × 4 = 12 combinations. A CROSS JOIN does exactly this — it pairs every row from table A with every row from table B.

The Syntax

SELECT columns
FROM table_a
CROSS JOIN table_b;

Notice: no ON clause! There's no matching condition because every row is paired with every other row.

Example: T-Shirt Combinations

-- Create two tiny helper tables for this demo
SELECT size, color
FROM (VALUES ('S'), ('M'), ('L')) AS sizes(size)
CROSS JOIN
     (VALUES ('Red'), ('Blue'), ('Green'), ('Black')) AS colors(color);

Result (12 rows):

size	color
S	Red
S	Blue
S	Green
S	Black
M	Red
M	Blue
M	Green
M	Black
L	Red
L	Blue
L	Green
L	Black

⚠️ WARNING — Explosive Result Size!

CROSS JOIN multiplies the row counts. If table A has 1,000 rows and table B has 1,000 rows, the result has 1,000,000 rows! With 10,000 × 10,000 tables, you get 100 million rows. This can crash your database or eat all your server's memory. Always think carefully before using CROSS JOIN, and use it only with small tables.

🌍 Real-Life Analogy

CROSS JOIN is like a round-robin tournament where every team plays against every other team. If you have 8 teams, that's 8 × 8 = 64 matchups (though some might be self-matches). In real-world SQL, CROSS JOINs are used for things like generating date calendars, building configuration matrices, or creating lookup grids.

7️⃣ Self JOIN — A Table Meets Itself

🧒 ELI5 — The Org Chart

Your company has an employees table. Each employee has a manager_id that points to another employee in the same table. To find out "who manages whom," you need to join the employees table to itself — comparing each employee's manager_id to another employee's id.

It's like looking at the same phone book twice — once to find yourself and once to find your boss.

Setup: An Employees Table

CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100),
    role       VARCHAR(50),
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (name, role, manager_id) VALUES
    ('Sara CEO',       'CEO',              NULL),
    ('Tom VP',         'VP Engineering',   1),
    ('Lisa VP',        'VP Marketing',     1),
    ('Jake Developer', 'Senior Developer', 2),
    ('Amy Designer',   'UI Designer',      2),
    ('Max Marketer',   'Content Writer',   3);

Example: Who Reports to Whom?

SELECT e.name AS employee,
       e.role,
       m.name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.id
ORDER BY e.id;

Result:

employee	role	manager
Sara CEO	CEO	NULL
Tom VP	VP Engineering	Sara CEO
Lisa VP	VP Marketing	Sara CEO
Jake Developer	Senior Developer	Tom VP
Amy Designer	UI Designer	Tom VP
Max Marketer	Content Writer	Lisa VP

Notice how the same table appears twice in the query — once as e (the employee) and once as m (the manager). Aliases are mandatory here; without them, PostgreSQL wouldn't know which "employees" you're referring to!

💡 Pro Tip — LEFT JOIN for Self JOINs

We used LEFT JOIN instead of INNER JOIN because the CEO (Sara) has manager_id = NULL — she doesn't report to anyone. An INNER JOIN would drop her from the results. LEFT JOIN keeps her and shows NULL for the manager column.

8️⃣ JOIN with Multiple Tables

🧒 ELI5 — The Chain of Connections

Think of it like a family tree. To find out "What did Grandma's daughter's son order for lunch?" you need to go through three connections: Grandma → Daughter → Grandson → Lunch Order. In SQL, you chain JOINs together to follow these relationships from one table to the next to the next.

Setup: A Categories Table

CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO categories (name) VALUES
    ('Computers'),
    ('Accessories'),
    ('Mobile');

-- Add a category_id column to our orders table
ALTER TABLE orders ADD COLUMN category_id INTEGER REFERENCES categories(id);

UPDATE orders SET category_id = 1 WHERE product IN ('Laptop', 'Monitor');
UPDATE orders SET category_id = 2 WHERE product IN ('Mouse', 'Keyboard', 'USB Hub', 'Webcam', 'Headphones', 'Laptop Stand');
UPDATE orders SET category_id = 3 WHERE product IN ('Tablet', 'Phone Case', 'Screen Protector', 'Stylus Pen');

Example: Customer → Order → Category (3-Table JOIN)

SELECT c.name AS customer,
       o.product,
       o.amount,
       cat.name AS category
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id
INNER JOIN categories cat
    ON o.category_id = cat.id
ORDER BY cat.name, o.amount DESC;

Result:

customer	product	amount	category
Bob Smith	Headphones	79.99	Accessories
Bob Smith	Laptop Stand	59.99	Accessories
Charlie Brown	Keyboard	149.99	Accessories
Alice Johnson	Webcam	89.99	Accessories
Charlie Brown	USB Hub	34.99	Accessories
Alice Johnson	Mouse	25.99	Accessories
Alice Johnson	Laptop	1200.00	Computers
Charlie Brown	Monitor	399.99	Computers
Diana Prince	Tablet	499.99	Mobile
Diana Prince	Stylus Pen	29.99	Mobile
Eve Williams	Phone Case	19.99	Mobile
Eve Williams	Screen Protector	9.99	Mobile

Multi-Table JOIN with Aggregation

-- Total revenue by category, showing who the top spender was
SELECT cat.name AS category,
       COUNT(o.id) AS order_count,
       SUM(o.amount) AS total_revenue
FROM categories cat
INNER JOIN orders o ON cat.id = o.category_id
INNER JOIN customers c ON o.customer_id = c.id
GROUP BY cat.name
ORDER BY total_revenue DESC;

Result:

category	order_count	total_revenue
Computers	2	1599.99
Mobile	4	559.96
Accessories	6	440.94

⚠️ Each JOIN Needs Its Own ON Clause!

A common mistake is forgetting the ON for one of the JOINs. Each JOIN must specify exactly how to match rows. Think of it as a chain: Table A links to Table B on one column, then Table B links to Table C on a different column. Every link in the chain needs its own rule.

9️⃣ Visual Comparison — All JOIN Types Side by Side

Let's put all the JOIN types together so you can see the big picture. Using our 8 customers and 12 orders data:

JOIN Type	What It Keeps	Result Rows	NULLs Appear?
INNER JOIN	Only rows that match in BOTH tables	12	No (unmatched rows are dropped)
LEFT JOIN	ALL rows from left + matches from right	15	Yes — right columns are NULL for unmatched left rows
RIGHT JOIN	ALL rows from right + matches from left	12	Yes — left columns are NULL for unmatched right rows
FULL OUTER JOIN	ALL rows from BOTH tables	15	Yes — on both sides for unmatched rows
CROSS JOIN	Every row × every row (no ON clause)	96 (8 × 12)	No — every combination is produced

INNER

The strict one
Only matched pairs

LEFT

The protective one
Keeps ALL left rows

RIGHT

The mirror of LEFT
Keeps ALL right rows

FULL

The inclusive one
Keeps EVERYTHING

CROSS

The wild one
Every × every

🧒 ELI5 — The Venn Diagram Way

Picture two overlapping circles. The left circle is the customers table. The right circle is the orders table. The overlap in the middle is where a customer has an order.

INNER JOIN = Only the overlap (middle part).

LEFT JOIN = The entire left circle + the overlap.

RIGHT JOIN = The overlap + the entire right circle.

FULL OUTER JOIN = Both entire circles, everything.

CROSS JOIN = Doesn't use a Venn diagram at all — it's a completely different concept (every possible pair).

💡 Pro Tip — Which JOIN Should I Use?

80% of the time: INNER JOIN or LEFT JOIN. Start with INNER JOIN when you only want matched data. Switch to LEFT JOIN when you need to keep all rows from the "main" table. Use FULL OUTER JOIN for data reconciliation. Avoid CROSS JOIN unless you specifically need all combinations. Self JOIN when you're looking at hierarchical data within the same table.

🔟 Common JOIN Mistakes

JOINs are powerful but tricky. Here are the mistakes that trip up beginners (and even experienced developers) the most.

Mistake 1: Forgetting the ON Clause

-- ❌ WRONG — No ON clause turns this into a CROSS JOIN!
SELECT c.name, o.product
FROM customers c
INNER JOIN orders o;
-- Result: 8 × 12 = 96 rows of nonsense!

-- ✅ CORRECT — Always include ON
SELECT c.name, o.product
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id;

Mistake 2: Joining on the Wrong Column

-- ❌ WRONG — Joining customer.id to order.id (both are just sequential numbers)
SELECT c.name, o.product
FROM customers c
INNER JOIN orders o
    ON c.id = o.id;
-- This matches customer #1 with order #1, customer #2 with order #2
-- That's completely WRONG — it's just coincidental number matching!

-- ✅ CORRECT — Join on the foreign key relationship
SELECT c.name, o.product
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id;

Mistake 3: Ambiguous Column Names

-- ❌ WRONG — Both tables have an "id" column. Which one do you mean?
SELECT id, name, product
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- ERROR: column reference "id" is ambiguous

-- ✅ CORRECT — Always prefix with the table alias
SELECT c.id AS customer_id, c.name, o.product
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Mistake 4: Accidental Cartesian Product

This happens when you join on a condition that matches too many rows, like joining on a column that has the same value in many rows (e.g., city = city).

-- ⚠️ DANGEROUS — Joining on city creates duplicates
SELECT c1.name, c2.name
FROM customers c1
INNER JOIN customers c2
    ON c1.city = c2.city
WHERE c1.id <> c2.id;
-- This finds customers in the SAME city
-- Alice & Frank both live in New York, so they'll match

Mistake 5: Using INNER JOIN When You Need LEFT JOIN

If your report says "Show ALL customers and their order totals" but some customers have $0 in orders, an INNER JOIN will silently drop those customers. Always ask yourself: "Do I need rows even if there's no match?" If yes → LEFT JOIN.

⚠️ The Golden Rule of JOINs

Before writing a JOIN, answer these three questions: (1) Which table is my "main" table? (2) Which column connects the two tables? (3) Do I need rows from the main table even when there's no match? If yes → LEFT JOIN. If no → INNER JOIN.

🏋️ Practice Exercises

Time to practice! Try solving these yourself using the customers and orders tables we created at the beginning. Click "Reveal Solution" after you've given it a try.

Exercise 1: The Order Report

Write a query that shows each order with the customer's name and city. Include the product, amount, and order_date. Sort by order_date ascending.

Reveal Solution

SELECT c.name, c.city, o.product, o.amount, o.order_date
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id
ORDER BY o.order_date ASC;

Exercise 2: The Lonely Customers

Find all customers who have never placed an order. Show their name, city, and email.

Reveal Solution

SELECT c.name, c.city, c.email
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.id IS NULL;

Returns: Frank Miller, Grace Lee, Hank Davis

Exercise 3: The Big Spender

Find the customer who has spent the most money in total. Show their name, total number of orders, and total amount spent.

Reveal Solution

SELECT c.name,
       COUNT(o.id) AS total_orders,
       SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 1;

Returns: Alice Johnson — 3 orders, $1,315.98 total

Exercise 4: The Complete Customer Report

Show ALL customers with their total spending. Customers with no orders should show 0 for total_spent and 0 for order_count. Sort by total_spent descending. (Hint: use COALESCE!)

Reveal Solution

SELECT c.name,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC;

Exercise 5: The City Summary

For each city, show the number of customers, the number of orders placed, and the total revenue. Include cities with zero orders. Sort by total revenue descending.

Reveal Solution

SELECT c.city,
       COUNT(DISTINCT c.id) AS customer_count,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.amount), 0) AS total_revenue
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
GROUP BY c.city
ORDER BY total_revenue DESC;

Exercise 6: Self JOIN Challenge

Using the employees table, find all employees whose manager is a VP (role contains 'VP'). Show the employee name, their role, and their manager's name.

Reveal Solution

SELECT e.name AS employee,
       e.role,
       m.name AS manager
FROM employees e
INNER JOIN employees m
    ON e.manager_id = m.id
WHERE m.role ILIKE '%VP%';

Returns: Jake Developer (managed by Tom VP), Amy Designer (managed by Tom VP), Max Marketer (managed by Lisa VP)

📝 Quiz — Test Your Knowledge!

Question 1: What does INNER JOIN do?

Question 2: You want to find customers who have NEVER placed an order. Which approach is correct?

Question 3: What is the difference between LEFT JOIN and RIGHT JOIN?

Question 4: If Table A has 5 rows and Table B has 4 rows, how many rows does a CROSS JOIN produce?

Question 5: When do you use a Self JOIN?

Question 6: What happens if you forget the ON clause in an INNER JOIN?

Question 7: Why are table aliases essential when doing a Self JOIN?

🎉 What's Next?

7

JOIN types mastered

20+

SQL examples practiced

7

Quiz questions conquered

Congratulations! You've conquered JOINs — the single most important concept in relational databases. Here's a quick recap:

JOIN Type	When to Use	Key Syntax
INNER JOIN	Only want matched rows	`FROM a INNER JOIN b ON a.id = b.a_id`
LEFT JOIN	Keep all rows from the main table	`FROM a LEFT JOIN b ON a.id = b.a_id`
RIGHT JOIN	Keep all from the second table (rare)	`FROM a RIGHT JOIN b ON a.id = b.a_id`
FULL OUTER JOIN	Keep everything from both	`FROM a FULL OUTER JOIN b ON ...`
CROSS JOIN	Every possible combination	`FROM a CROSS JOIN b`
Self JOIN	Compare rows within the same table	`FROM a x JOIN a y ON x.col = y.col`
Multi-Table JOIN	Chain 3+ tables together	`FROM a JOIN b ON ... JOIN c ON ...`

🔮 Coming Up Next: Subqueries & CTEs

Now that you can combine tables, it's time to learn about queries inside queries! Subqueries let you use the result of one query as input to another — like a Russian nesting doll of SQL. And CTEs (Common Table Expressions) let you name and reuse complex query parts, making your SQL cleaner and more readable. Think of it as going from "one big messy query" to "organized, modular building blocks."

🤝 JOINs — Combining Tables

📦 Our Practice Tables

🧒 ELI5 — Why Two Tables?

💡 Pro Tip — Notice the Key Relationship!

1️⃣ Why JOINs?

🧒 ELI5 — The Pizza Order Problem

⚠️ Without JOINs, Relational Databases Are Useless!

2️⃣ INNER JOIN — Only the Matches

🧒 ELI5 — The Buddy System

The Syntax

Example 1: Who Ordered What?

⚠️ Where Did Frank, Grace, and Hank Go?

Example 2: Total Spent Per Customer

💡 Pro Tip — Table Aliases Save Typing!

🌍 Real-Life Analogy

3️⃣ LEFT JOIN (LEFT OUTER JOIN) — Keep All Left Rows

🧒 ELI5 — The Class Photo

The Syntax

Example: All Customers + Their Orders (If Any)

Killer Use Case: Find Customers Who NEVER Ordered

🌍 Real-Life Analogy

💡 Pro Tip — LEFT JOIN vs LEFT OUTER JOIN

4️⃣ RIGHT JOIN — Keep All Right Rows

🧒 ELI5 — The Mirror Image

Example: All Orders + Customer Info (If Available)

⚠️ RIGHT JOIN Is Rarely Used in Practice

5️⃣ FULL OUTER JOIN — Everything From Both Sides

🧒 ELI5 — Merging Two Party Guest Lists

The Syntax

Example: Full Picture of Customers and Orders

🌍 Real-Life Analogy

💡 Pro Tip — When to Use FULL OUTER JOIN

6️⃣ CROSS JOIN — The Cartesian Product

🧒 ELI5 — Every T-Shirt × Every Color

The Syntax

Example: T-Shirt Combinations

⚠️ WARNING — Explosive Result Size!

🌍 Real-Life Analogy

7️⃣ Self JOIN — A Table Meets Itself

🧒 ELI5 — The Org Chart

Setup: An Employees Table

Example: Who Reports to Whom?

💡 Pro Tip — LEFT JOIN for Self JOINs

8️⃣ JOIN with Multiple Tables

🧒 ELI5 — The Chain of Connections

Setup: A Categories Table

Example: Customer → Order → Category (3-Table JOIN)

Multi-Table JOIN with Aggregation

⚠️ Each JOIN Needs Its Own ON Clause!

9️⃣ Visual Comparison — All JOIN Types Side by Side

🧒 ELI5 — The Venn Diagram Way

💡 Pro Tip — Which JOIN Should I Use?

🔟 Common JOIN Mistakes

Mistake 1: Forgetting the ON Clause

Mistake 2: Joining on the Wrong Column

Mistake 3: Ambiguous Column Names

Mistake 4: Accidental Cartesian Product

Mistake 5: Using INNER JOIN When You Need LEFT JOIN

⚠️ The Golden Rule of JOINs

🏋️ Practice Exercises

Exercise 1: The Order Report

Exercise 2: The Lonely Customers

Exercise 3: The Big Spender

Exercise 4: The Complete Customer Report

Exercise 5: The City Summary

Exercise 6: Self JOIN Challenge

📝 Quiz — Test Your Knowledge!

Question 1: What does INNER JOIN do?

Question 2: You want to find customers who have NEVER placed an order. Which approach is correct?

Question 3: What is the difference between LEFT JOIN and RIGHT JOIN?

Question 4: If Table A has 5 rows and Table B has 4 rows, how many rows does a CROSS JOIN produce?

Question 5: When do you use a Self JOIN?

Question 6: What happens if you forget the ON clause in an INNER JOIN?

Question 7: Why are table aliases essential when doing a Self JOIN?

🎉 What's Next?

🔮 Coming Up Next: Subqueries & CTEs