JOINs: Combining Data From Multiple Tables

Real data almost never lives in a single table. Splitting related data across multiple tables — a practice called normalization — avoids repeating the same information over and over, and JOINs are how you bring that split data back together for a query.

Say an enrollments table links student_id to course_id, a separate students table holds student details, and a courses table holds course names — this is a far more realistic structure than one giant table with every student’s every course crammed into a single row.

INNER JOIN

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

INNER JOIN returns only rows that have a match in both tables being joined — a student with no enrollments, or an enrollment pointing to a deleted course, simply wouldn’t appear in these results at all.

LEFT JOIN

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

LEFT JOIN returns every row from the left table (students), even those with no match in the right table — a student with no enrollment still appears, with NULL shown for course_id. Use LEFT JOIN whenever you need to include rows that might not have a match, such as “show me every student, and their course if they have one.”

Table aliases

SELECT s.name, c.title
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id;

Once a query joins more than one or two tables, writing the full table name before every column becomes tedious. Aliases (students s, courses c) shorten this considerably, and are standard practice in any query with multiple JOINs.

A common mistake

Forgetting the ON clause, or joining on the wrong columns, produces a cross join — every row from one table paired with every row from the other, which can silently multiply your row count many times over and quietly corrupt any aggregate calculation built on top of it. Always double-check that a JOIN’s row count looks reasonable before trusting the results.