Joining tables · Lesson 9 of 20

INNER JOIN

Combine rows from two tables where they match.

9 minutes3 games

JOIN pairs each row of one table with the rows that match it in another, using a condition written after ON. Usually that's "foreign key = primary key".

SQL

SELECT s.name, e.course_id, e.progress
FROM students AS s
JOIN enrollments AS e ON e.student_id = s.id
WHERE s.city = 'Cairo';

s and e are short table names (aliases); they save typing and show where each column comes from. If a column exists in both tables (like id) you must prefix it.

students	enrollments	In the result?
Laila (id 1)	3 rows (1,1) (1,2) (1,5)	yes, 3 times
Omar (id 3)	1 row (3,3)	yes, once
Mariam (id 6)	2 rows	yes, twice
Youssef (id 5)	none	no, gone

A student repeats once per enrollment; one with none disappears.

What's the output?

SELECT COUNT(*) AS rows
FROM students s
JOIN enrollments e ON e.student_id = s.id;

What will this code print?

Spot the bug

The result is wrong: course names that don't belong to these students. Where's the bug?

Exercise

Get the names of students enrolled in course 3 (Python), in alphabetical order.

Expected result:

name
Ali
Nour
Omar

SQL

SELECT s.name
FROM students s

Learn more from

Played 0 of 3 games

SQL