SQL Joins Explained with Examples: INNER, LEFT, RIGHT and FULL

By Janidu Sandaruwan · Updated October 2026 · 3 min read

Joins are where SQL starts to feel powerful, and also where most query mistakes happen. The trick is to stop memorising diagrams and instead ask one question each time: which rows do I want to keep when there is no match?

1. The example tables

Note that Dilan (3) has no enrollments, and enrollment row 4 has no matching student.

2. INNER JOIN: only matches

SELECT s.name, e.module, e.grade
FROM Student s
INNER JOIN Enrollment e ON s.student_id = e.student_id;

Result: Amaya–IP, Amaya–MC, Kavin–IP. Three rows. Dilan disappears (no enrollment), and the student_id 4 enrollment disappears (no student). INNER JOIN keeps only rows that match on both sides.

3. LEFT JOIN: keep everything on the left

SELECT s.name, e.module
FROM Student s
LEFT JOIN Enrollment e ON s.student_id = e.student_id;

Result: the three matched rows plus Dilan with NULL for module. Every row from the left table (Student) is kept; where there's no match, the right side columns are NULL. Use it for questions like "list all students and their modules, including students with none".

4. RIGHT and FULL OUTER JOIN

RIGHT JOIN is the mirror image: it keeps every row from the right table. Here it would return the three matches plus the DCN enrollment with a NULL name. Most people rewrite RIGHT JOINs as LEFT JOINs by swapping the table order, which is easier to read.

FULL OUTER JOIN keeps unmatched rows from both sides: the three matches, Dilan with a NULL module, and the DCN enrollment with a NULL name. (MySQL doesn't support FULL OUTER JOIN directly; you can combine a LEFT and a RIGHT join with UNION.)

5. Finding unmatched rows

A very common exam question: "Find students who are not enrolled in any module." Use a LEFT JOIN and keep only the rows where the right side is NULL:

SELECT s.name
FROM Student s
LEFT JOIN Enrollment e ON s.student_id = e.student_id
WHERE e.student_id IS NULL;

Result: Dilan. Note it's IS NULL, not = NULL; comparing with = never matches NULL.

6. Joining three tables

Add one JOIN per extra table, each with its own ON condition. If a Module table holds module names and credits:

SELECT s.name, m.module_name, m.credits, e.grade
FROM Student s
JOIN Enrollment e ON s.student_id = e.student_id
JOIN Module m     ON e.module = m.module_code;

7. Common mistakes

• Missing ON condition: produces a Cartesian product, where every row is paired with every row.
• Filtering a LEFT JOIN in WHERE: a condition on the right table in WHERE (other than IS NULL) silently turns it into an inner join. Put such conditions in the ON clause instead.
• Ambiguous column names: always prefix with table aliases (s.student_id) when both tables have the column.
• Using = NULL instead of IS NULL.