SQL: difference between INNER JOIN, LEFT JOIN and RIGHT JOIN?

Question

I have `students` and `enrollments`. Some students have no enrollment. Which JOIN shows all students, including those without enrollments?

Accepted Answer

- **INNER JOIN**: only rows that match in **both** tables. Students without enrollments disappear.
- **LEFT JOIN**: **all rows from the left** table, plus matches from the right. Where there's no match, the right-side columns are `NULL`.
- **RIGHT JOIN**: the mirror of LEFT. Rarely used, since you can just swap the table order.

```sql
SELECT s.name, e.course_id
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id;
```

To find students with **no** enrollment:

```sql
SELECT s.*
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;
```

Careful: a condition on the right table in `WHERE` (e.g. `WHERE e.year = 2026`) turns your LEFT JOIN back into an INNER JOIN. Put it in the `ON` clause instead.

2 Answers