Lesson 6 • Intermediate
JOIN Operations
Combine data from multiple tables — the skill that separates SQL beginners from intermediates.
🎯 What You'll Learn
- INNER JOIN: rows that match in both tables
- LEFT/RIGHT JOIN: keep all rows from one side
- FULL OUTER JOIN and CROSS JOIN
- Join 3+ tables in a single query
- Self-joins: joining a table to itself
Why JOINs Matter
Real databases split data across multiple tables to avoid duplication. A customer's name is stored once in a customers table, and orders reference the customer by ID. JOINs stitch these tables back together.
🧩 Real-World Analogy
Think of JOINs like matching puzzle pieces. Table A has customer names, Table B has their orders. A JOIN connects each customer to their orders using a shared piece — the customer_id.
JOIN Types at a Glance
| Type | Returns | Use When |
|---|---|---|
| INNER JOIN | Only matching rows | You only want records that exist in both |
| LEFT JOIN | All left + matching right | Keep all from left, even without matches |
| RIGHT JOIN | All right + matching left | Mirror of LEFT JOIN |
| FULL OUTER | All from both sides | Find unmatched records on either side |
| CROSS JOIN | Every possible combination | Generate all possible pairs |
INNER JOIN
Get only rows that match in both tables
-- INNER JOIN: only matching rows from BOTH tables
SELECT
o.order_id,
c.first_name,
c.last_name,
o.total,
o.order_date
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;
-- Table aliases (o, c) make queries shorter
-- ON clause specifies the matching conditionLEFT & RIGHT JOIN
Keep all rows from one table, even without matches
-- LEFT JOIN: ALL rows from left + matches from right
-- Non-matching right rows show NULL
SELECT c.first_name, c.last_name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Find customers who NEVER ordered
SELECT 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;
-- RIGHT JOIN: mirror of LEFT JOIN
SELECT o.order_id, c.first_name
FROM customers c
RIGHT JOIN orders o ON c.customer_i
...FULL OUTER & CROSS JOIN
Get all rows from both tables or every combination
-- FULL OUTER JOIN: ALL rows from BOTH tables
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
-- Shows ALL customers AND ALL orders
-- NULLs where no match exists
-- CROSS JOIN: every combination (cartesian product)
SELECT p.product_name, s.size_name
FROM products p
CROSS JOIN sizes s;
-- If products has 10 rows and sizes has 5,
-- result has 50 rows (10 × 5)Multi-Table JOINs
You can chain JOINs to connect 3, 4, or even more tables. Each JOIN adds another link in the chain.
⚠️ Common Mistake
Forgetting the ON clause in a JOIN — this creates a CROSS JOIN (cartesian product) and can return millions of unintended rows!
💡 Pro Tip
Always use table aliases (FROM orders o) when joining. Without aliases, multi-table queries become unreadable. Convention: use the first letter or abbreviation of the table name.
Join 3+ Tables
Real-world order detail report joining orders, customers, items, and products
-- Join 3+ tables: real-world order details
SELECT
c.first_name || ' ' || c.last_name AS customer,
o.order_id,
p.product_name,
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.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_id, p.product_name;Self-Joins
Join a table to itself for hierarchical or pair-wise queries
-- SELF JOIN: join a table to itself
-- Find employees and their managers
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
-- Find pairs of products in same category
SELECT
a.product_name AS product_1,
b.product_name AS product_2,
a.category
FROM products a
INNER JOIN products b
ON a.category = b.category
AND a.product_id < b.product_id;📘 Quick Reference
| Pattern | Use |
|---|---|
| A INNER JOIN B ON A.id = B.a_id | Only matches |
| A LEFT JOIN B ON ... WHERE B.id IS NULL | In A but not B |
| A JOIN B ON ... JOIN C ON ... | Chain multiple tables |
| A JOIN A ON A.parent = A.id | Self-join |
🎉 Lesson Complete!
You can now combine data from multiple tables using JOINs — one of the most powerful features in SQL. Next, you'll learn aggregate functions to calculate totals, averages, and counts!
Sign up for free to track which lessons you've completed and get learning reminders.