Joining Tables: INNER JOIN

Bring rows from two (or three) tables together into one result

Big Idea: Real databases split information across many tables to avoid repeating it. An INNER JOIN stitches those tables back together, matching rows with a shared value so you can read them as one wide result.

By the end you will be able to:

explain why data lives in more than one table,
write an INNER JOIN with an ON condition,
use short table aliases to keep queries tidy,
join three tables in a chain,
predict which rows an INNER JOIN keeps and which it drops.

1. Why data lives in many tables

Imagine a school stored everything in one giant table: every student row also repeated the full class name and the teacher's name. If Math is taught by Mr. Adams, you would type "Math" and "Mr. Adams" again for every single student in that class. That wastes space and, worse, invites mistakes: fix the teacher's name in one row and forget the others, and now your data disagrees with itself.

The tidy solution is to keep each kind of thing in its own table and connect them with keys (you met primary and foreign keys in the last topic):

a students table (one row per student),
a classes table (one row per class),
a teachers table (one row per teacher).

Each student row stores a small class_id instead of the whole class name. The name is written once in classes. To see the class name next to a student, we ask the database to join the two tables back together.

2. Our example school database

We will use this tiny database for the whole lesson. Create it once, then run the queries below against it.

CREATE TABLE teachers (
    teacher_id   INT PRIMARY KEY,
    teacher_name VARCHAR(50)
);

INSERT INTO teachers VALUES
    (1, 'Mr. Adams'),
    (2, 'Ms. Baker'),
    (3, 'Mr. Carter');

CREATE TABLE classes (
    class_id   INT PRIMARY KEY,
    class_name VARCHAR(50),
    teacher_id INT
);

INSERT INTO classes VALUES
    (101, 'Math',    1),
    (102, 'Science', 2),
    (103, 'Art',     3);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(50),
    class_id   INT
);

INSERT INTO students VALUES
    (1, 'Alice', 101),
    (2, 'Ben',   101),
    (3, 'Carla', 102),
    (4, 'Dan',   103),
    (5, 'Eve',   NULL);

So the three tables hold:

teachers

teacher_id	teacher_name
1	Mr. Adams
2	Ms. Baker
3	Mr. Carter

classes

class_id	class_name	teacher_id
101	Math	1
102	Science	2
103	Art	3

students

student_id	name	class_id
1	Alice	101
2	Ben	101
3	Carla	102
4	Dan	103
5	Eve	NULL

Notice Eve has class_id = NULL — she has not joined a class yet. Keep an eye on her; she will help us understand what INNER JOIN leaves out.

Joining Tables: INNER JOIN