INNER JOIN is the most common type of join. It returns only the rows where there is a match in both tables. If a student is not enrolled in any course, they do not show up. If a course has no students, also invisible.
Basic INNER JOIN
-- Get student names with their course names
SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;See what happened? We joined three tables together. Students to enrollments, then enrollments to courses. The result shows which students are taking which courses.
Table Aliases
Writing full table names gets tiring. Use aliases to keep things clean:
SELECT s.name, c.course_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;
s, e, and c are aliases. Much shorter,
and once you get used to it, actually easier to read.
INNER JOIN with Extra Conditions
-- Students in Mathematics with their grades
SELECT s.name, e.grade
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id
WHERE c.course_name = 'Mathematics';