Joins are SQL's most powerful operation — and the #1 reason analytics dashboards return wrong numbers. In this lesson you'll learn the four join types, the cardinality traps that break production, and how to debug a join that "should work" but multiplies rows by 17x.
Learning Objectives
After this lesson, you will be able to:
Explain why data is split across multiple tables (normalization) and why JOINs are needed
Write an INNER JOIN to combine two tables on a matching column
Use LEFT JOIN to keep all rows from the left table even without a match
Understand RIGHT JOIN and FULL JOIN and when you would use them
Write a self-join to connect a table to itself (like finding each employee's manager)
Use table aliases (e, m, o) to write cleaner JOIN queries
Combine JOINs with WHERE, GROUP BY, and ORDER BY for complex analysis
Diagnose the cardinality explosion: when a one-to-many join silently inflates SUM/COUNT
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
JOINs are the single most important SQL concept you will learn. If SELECT is learning to read, JOINs are learning to connect ideas across different books. This lesson takes more time, but the payoff is massive. Every real-world SQL query uses at least one JOIN.
Think about a school database. You could store everything in one giant table:
See the problem? Mr. Smith's name and email are repeated on every row for every student in his class. If he changes his email, you would need to update hundreds of rows. One missed update and your data is inconsistent.
The solution: split data into separate tables and connect them:
students table: student info (id, name, email)
classes table: class info (id, name, teacher_id)
teachers table: teacher info (id, name, email)
enrollments table: which student is in which class (student_id, class_id, grade)
Each piece of data is stored exactly once. To combine them, you use a JOIN.
If an order references a product that does not exist in the products table, that order row is dropped. If a product has never been ordered, it does not appear. INNER JOIN keeps only the intersection -- rows with matches on both sides.
INNER JOIN -- Only Matching Rows
Left Table (orders)
Match?
Right Table (products)
In Result?
Order for Widget A
Yes
Widget A exists
Included
Order for Widget B
Yes
Widget B exists
Included
Order for Widget Z
No
Widget Z does not exist
Excluded
(no order)
No
Widget D exists but never ordered
Excluded
What Do You Think?
If the orders table has 10 rows and the products table has 5 rows, how many rows does an INNER JOIN return?
The answer is it depends on how many rows match the ON condition. If every order matches a product, you get 10 rows. If some orders reference products that do not exist, you get fewer. If some orders match the same product, you might even get more than 10 (though in this case, that does not apply).
LEFT JOIN returns ALL rows from the left table, even if there is no match in the right table. When there is no match, the right table's columns are filled with NULL.
sql
-- All employees with their manager's name (even employees with no manager)
SELECT
e.name AS employee,
e.department,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Employees without a manager (manager_id is NULL) still appear in the results -- their manager column just shows NULL.
LEFT JOIN -- Keep All Left Rows
Employee (Left)
manager_id
Manager Match?
Manager (Right)
Alice Johnson
NULL
No match
NULL
Bob Smith
1
Matches Alice
Alice Johnson
Carol Davis
NULL
No match
NULL
David Wilson
1
Matches Alice
Alice Johnson
Eve Brown
3
Matches Carol
Carol Davis
Try it! Run this LEFT JOIN to see every employee and their manager. Notice the NULL values for top-level employees.
INNER JOIN -- "I only want rows that have matches in BOTH tables." Use when missing data means the row is not useful
LEFT JOIN -- "I want ALL rows from the left table, with bonus info from the right table if it exists." Use when you want to keep everything and see what is missing
A common pattern: LEFT JOIN then check for NULLs to find unmatched rows:
sql
-- Find employees who have no manager (top-level employees)
SELECT e.name, e.department
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE m.id IS NULL;
RIGHT JOIN is the mirror of LEFT JOIN. It keeps ALL rows from the RIGHT table, even without matches in the left:
sql
-- All products, even those never ordered
SELECT p.name, p.category, o.customer_name, o.amount
FROM orders o
RIGHT JOIN products p ON o.product = p.name;
Products that were never ordered will show NULL for order columns. In practice, most developers just swap the table order and use LEFT JOIN instead of RIGHT JOIN. They are functionally equivalent:
sql
-- These two queries produce the same results:
-- RIGHT JOIN version
SELECT * FROM orders o RIGHT JOIN products p ON o.product = p.name;
-- LEFT JOIN version (swap table order)
SELECT * FROM products p LEFT JOIN orders o ON o.product = p.name;
FULL JOIN keeps ALL rows from BOTH tables. No row is excluded:
sql
SELECT e.name AS employee, m.name AS manager
FROM employees e
FULL JOIN employees m ON e.manager_id = m.id;
Rows from the left with no right match: right columns are NULL
Rows from the right with no left match: left columns are NULL
FULL JOIN is less common but useful when you need to see everything from both sides, like reconciling two datasets.
Note: SQLite (used in this playground) does not support RIGHT JOIN or FULL JOIN directly. Use LEFT JOIN with swapped table order instead. Most production databases (PostgreSQL, MySQL 8+, SQL Server) support all join types.
What Do You Think?
What is the key difference between INNER JOIN and LEFT JOIN?
The answer is LEFT JOIN returns all rows from the left table even without matches; INNER JOIN only returns matching rows. That is the core difference. LEFT JOIN guarantees no rows from the left table are lost. INNER JOIN only keeps rows with successful matches on both sides.
A self-join connects a table to itself. This is essential when a table has a relationship within its own rows -- like employees and their managers (both stored in the employees table).
sql
-- Each employee with their manager's name
SELECT
e.name AS employee,
e.department,
m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
The trick: you use the SAME table twice with DIFFERENT aliases (e for the employee, m for the manager). SQL treats them as two separate copies of the table.
#Finding Employees Who Earn More Than Their Manager
Self-joins let you compare rows within the same table:
sql
SELECT
e.name AS employee,
e.salary AS employee_salary,
m.name AS manager,
m.salary AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
This finds employees whose salary exceeds their manager's salary. Without a self-join, you would need complex subqueries.
Try it! Find employees who earn more than their manager. Are there any?
The true power of JOINs shines when you combine them with GROUP BY and aggregates:
sql
-- Revenue per product category
SELECT
p.category,
COUNT(*) AS num_orders,
SUM(o.amount) AS total_revenue,
ROUND(AVG(o.amount), 2) AS avg_order
FROM orders o
INNER JOIN products p ON o.product = p.name
GROUP BY p.category
ORDER BY total_revenue DESC;
This joins orders with products to get the category, then groups by category to get revenue summaries. This is the kind of query that powers business dashboards.
-- How many direct reports does each manager have?
SELECT
m.name AS manager,
m.department,
COUNT(e.id) AS direct_reports
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.name, m.department
ORDER BY direct_reports DESC;
Try it! Find the total revenue per product category.
-- Add product details to every order
SELECT o.id, o.customer_name, o.product, p.category, p.price
FROM orders o
INNER JOIN products p ON o.product = p.name;
-- Products that have never been ordered
SELECT p.name, p.category, p.price
FROM products p
LEFT JOIN orders o ON p.name = o.product
WHERE o.id IS NULL;
-- Top customers by total spending
SELECT o.customer_name, SUM(o.amount) AS total_spent, COUNT(*) AS num_orders
FROM orders o
INNER JOIN products p ON o.product = p.name
GROUP BY o.customer_name
ORDER BY total_spent DESC
LIMIT 3;
-- Organization chart: employee -> manager
SELECT
e.name AS employee,
COALESCE(m.name, '(No Manager)') AS reports_to
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
The COALESCE function returns the first non-NULL value. So if m.name is NULL (no manager), it shows '(No Manager)' instead.
JOINs combine data from multiple tables using a matching condition specified in the ON clause
INNER JOIN keeps only matching rows -- rows without a match in either table are excluded
LEFT JOIN keeps all rows from the left table -- unmatched rows get NULL for the right table's columns
Self-joins connect a table to itself -- essential for hierarchies like employee-manager relationships
Table aliases (e, m, o, p) make JOIN queries readable. Always use them
JOINs + GROUP BY = powerful analytics -- combine tables and then aggregate for real business insights
COALESCE handles NULLs -- COALESCE(value, 'default') returns the first non-NULL argument
Always know your cardinality before joining. When in doubt, aggregate first then join, or use COUNT(DISTINCT ...) to detect inflated rows
Quick Check1 / 5
What does the ON clause do in a JOIN?
What's next: Subqueries and CTEs (WITH clauses). Now that you can join and aggregate, you'll learn how to compose multi-step queries — running a query, capturing the result as a named table, then querying that. CTEs are how senior engineers structure analytical SQL. Once you write a few, you'll never go back to nested subqueries.