Lesson 28 of 61

5 mins read

SQL INNER JOIN: Matching Intersections Across Tables

In Plain English

The `INNER JOIN` (or simply `JOIN`) keyword selects records that have matching values in BOTH tables. If a row in the left table has no matching record in the right table, it is excluded from the result set.

The Mental Model

"An `INNER JOIN` is like organizing a doubles tennis match: only pairs with both Player A and Player B ready are allowed onto the court; solo players are left in the waiting room."

Deep Dive: How It Works

Intersection Logic: Returns the strict mathematical intersection (A ∩ B) of two relations.

JOIN vs INNER JOIN: In ANSI SQL, writing `JOIN` is completely identical to `INNER JOIN`.

Multi-Table Joins: `FROM orders o JOIN customers c ON o.customer_id = c.id JOIN payments p ON o.id = p.order_id`.

Syntax Blueprint

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

INNER JOIN returns rows only where the ON condition matches in both tables.

Core Rules to Remember

Strict Intersection: Unmatched rows from either table are excluded from results.

Multi-Table Chaining: You can chain 3, 5, or 10 tables together sequentially in one query.

Explicit Alias Usage: Qualify column names with table aliases to prevent ambiguous column errors.

Common Beginner Traps & How to Fix Them

Mistake:Using INNER JOIN when you need to see customers who have made ZERO orders.

Why it happens: Customers with 0 orders have no matching foreign key rows and are dropped by INNER JOIN.

How to fix: Use `LEFT JOIN` instead to preserve customers with no orders.

Pro Tip: If both tables share the EXACT same column name for the key, you can use the concise `USING (customer_id)` syntax.

Live Interactive Example

Hit Run Code to see it live

3-Table INNER JOIN: Customers, Orders, and Products

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The 3-table INNER JOIN correlated customer identity with purchased products.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Perform an INNER JOIN

Select `e.name`, `d.dept_name` from `employees e INNER JOIN departments d ON e.dept_id = d.id`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.