Inner joins — The Ultimate SQL Mastery

Matching rows across two tables, and what quietly disappears when nothing matches.

Data worth querying is rarely in one table. A join matches rows in one table against rows in another using a condition you supply.

SELECT students.name, courses.title
FROM students
JOIN enrolments ON enrolments.student_id = students.id
JOIN courses    ON courses.id = enrolments.course_id;

What "inner" means

An inner join keeps only rows that matched. A student enrolled in nothing does not appear. Neither does a course nobody took.

This is usually what you want, and occasionally a trap: "how many students do we have?" answered with the query above returns how many students have enrolments. The number will look plausible and be wrong.

The join condition is not optional

Omit ON and most databases will give you every combination of every row — a thousand students against a hundred courses is a hundred thousand rows. The query does not fail. It just returns something enormous and meaningless.

Joining does not mean nesting

The order you write joins in is not the order the database performs them. The planner reorders freely based on what it knows about the size of each table. Write the joins in whatever order reads most clearly to a human; that is the only audience whose order actually matters.