SQL INNER JOIN explained for beginners

Joining two tables

Why not keep everything in one giant table? Because repeating yourself is how mistakes happen. If every student row said Robotics, Room B12, then moving Robotics to a new room means editing dozens of rows, and missing one. Instead, each club is stored once in a clubs table, and each student just stores the club's id in a column called club_id.

That club_id is a reference to another table. To read it back as a real club name, you join the tables:

SELECT students.name, clubs.name
FROM students
INNER JOIN clubs ON students.club_id = clubs.id;

How to read it: take every student, find the club whose id equals that student's club_id, and glue the two rows together side by side. The ON part is the matching rule. Get it wrong (say, students.id = clubs.id) and you get confident nonsense, so it is worth a second look every time.

Both tables have a column called name, so you must say which one you mean with table.column. Typing students. over and over is tiring, so people give tables short nicknames:

SELECT s.name, c.name AS club
FROM students AS s
INNER JOIN clubs AS c ON s.club_id = c.id;

INNER means "only keep rows that found a match". A student whose club_id is NULL matches no club, so they vanish from the result. A club nobody joined vanishes too. Often that is what you want. When it isn't, you need the next lesson.

After the join you have one wide result, and everything you already know still works on it: WHERE, ORDER BY, GROUP BY, the lot.

Tables in this lesson

clubs 5 rows

id	name	room
1	Robotics	B12
2	Chess	A03
3	Drama	Hall
4	Esports	B12
5	Knitting	A07

students 9 rows

id	name	year	club_id
1	Aisha	10	2
2	Ben	11	3
3	Chloe	10	1
4	Dev	12	2
5	Ella	11	1
6	Finn	10	NULL
7	Grace	12	4
8	Hugo	11	4
9	Ivy	12	NULL

Show the SQL that built them

CREATE TABLE clubs (id INTEGER PRIMARY KEY, name TEXT, room TEXT);
INSERT INTO clubs VALUES
 (1, 'Robotics', 'B12'),
 (2, 'Chess', 'A03'),
 (3, 'Drama', 'Hall'),
 (4, 'Esports', 'B12'),
 (5, 'Knitting', 'A07');
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, year INTEGER, club_id INTEGER);
INSERT INTO students VALUES
 (1, 'Aisha', 10, 2),
 (2, 'Ben', 11, 3),
 (3, 'Chloe', 10, 1),
 (4, 'Dev', 12, 2),
 (5, 'Ella', 11, 1),
 (6, 'Finn', 10, NULL),
 (7, 'Grace', 12, 4),
 (8, 'Hugo', 11, 4),
 (9, 'Ivy', 12, NULL);

Try it yourself

Edit it. Break it. Run it again.

SELECT s.name, c.name AS club, c.room
FROM students AS s
INNER JOIN clubs AS c ON s.club_id = c.id;

Your turn

Type it yourself. That is the whole trick.

1Who meets where

Return each student's name together with the room their club meets in. Only students who are in a club.

SELECT s.name, s.club_id
FROM students AS s;

2Room B12 crowd

Return the name of every student whose club meets in room B12.

SELECT s.name
FROM students AS s
INNER JOIN clubs AS c ON s.club_id = c.id;