Join types explained

Given students and enrolments:

• INNER JOIN — only pairs that match on both sides.
• LEFT JOIN — every row from the left table; NULLs on the right where nothing matches.
• RIGHT JOIN — the mirror image (usually rewritten as a LEFT JOIN).
• FULL OUTER JOIN — every row from both sides, matched where possible.
• CROSS JOIN — every combination (n × m rows); no condition.
• Self join — a table joined to itself under two aliases (employee → manager).

-- Students with no enrolment (anti-join)
select s.id, s.name
from students s
left join enrolments e on e.student_id = s.id
where e.student_id is null;

-- Equivalent and often clearer
select s.id, s.name from students s
where not exists (select 1 from enrolments e where e.student_id = s.id);

A classic trap: putting a condition on the right table in WHERE (where e.semester = 5) after a LEFT JOIN removes the NULL rows and silently turns it into an inner join. Put such conditions in the ON clause instead.