Joining Tables

Relational databases spread data across multiple tables, a JOIN combines rows from two tables based on a shared column.

Given students(id, name) and enrollments(student_id, course_title):

SELECT students.name, enrollments.course_title
FROM students
JOIN enrollments ON students.id = enrollments.student_id;

ON students.id = enrollments.student_id tells the database how to match rows between the two tables.

INNER JOIN vs. LEFT JOIN

• A plain JOIN (short for INNER JOIN) only returns rows that have a match in both tables.
• A LEFT JOIN keeps every row from the left (first) table, filling in NULL for columns from the right table when there's no match, useful for finding students with zero enrollments.

SELECT students.name, enrollments.course_title
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;