A JOIN combines rows from two tables when their values satisfy a condition. INNER, LEFT, RIGHT, and FULL OUTER JOIN differ in whether unmatched rows are discarded or kept with NULL values.
A simulator showing how JOIN conditions combine rows into a result table
1. Choose a JOIN type
2. Match rows one at a time
Press Next step to take each student row and find enrollments with the same student ID. The shaded part of the Venn diagram indicates which side is retained.
OUTER JOIN retains unmatched rows with NULLs
LEFT JOIN keeps every row from the left table. If no right-hand row matches, its columns become NULL. Students with no enrollments still appear, so to find them, follow the LEFT JOIN with WHERE e.student_id IS NULL.
Multiple matches duplicate the left-hand row. If Haneul Kim takes two courses, the result contains two rows for that student. Remember this multiplicity when COUNT after a JOIN is larger than expected.
Common interview topic
A WHERE condition on the right table after a LEFT JOIN can filter out NULL rows, effectively making it an INNER JOIN. Put the condition in ON when you want to filter the right side but preserve every left row. CROSS JOIN produces all combinations, for m × n rows.