SQL joins explained for students
7 min readUpdated
A join combines rows from two tables using a column they share. Joins appear in database exams, coursework and interviews, and they are easy once you picture which rows each one keeps.
Two small tables
| student_id | name |
|---|---|
| 1 | Asha |
| 2 | Ben |
| 3 | Chen |
| student_id | course |
|---|---|
| 1 | Maths |
| 1 | Physics |
| 2 | History |
| 4 | Art |
INNER JOIN: only matches
SELECT s.name, e.course
FROM students s
INNER JOIN enrolments e ON s.student_id = e.student_id;Returns Asha–Maths, Asha–Physics and Ben–History. Chen has no enrolment and enrolment 4 has no student, so both are left out.
LEFT JOIN: every row from the left
SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON s.student_id = e.student_id;Same three rows plus Chen with NULL for course. Use it to find missing data: add WHERE e.student_id IS NULL to list students with no enrolment.
RIGHT and FULL OUTER JOIN
A RIGHT JOIN keeps every row from the right table, so the Art enrolment appears with a NULL name. A FULL OUTER JOIN keeps unmatched rows from both sides. Some databases, such as MySQL, do not support FULL OUTER JOIN directly; you combine a LEFT and a RIGHT join with UNION.
| Join | Keeps |
|---|---|
| INNER | Only rows that match on both sides |
| LEFT | All left rows, plus matches |
| RIGHT | All right rows, plus matches |
| FULL OUTER | All rows from both sides |
Self join
A table can join to itself, for example to pair each employee with their manager stored in the same table.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Common mistakes
- Leaving out the join condition, which pairs every row with every row.
- Putting a filter on the right table in
WHEREafter a LEFT JOIN, which silently turns it into an inner join; put it inONinstead. - Ambiguous column names: prefix them with the table alias.
- Counting with
COUNT(*)after a LEFT JOIN, which counts the unmatched rows too; useCOUNT(e.course).
Frequently asked questions
Is JOIN the same as INNER JOIN?
Yes. Writing just JOIN means an inner join.
Which join should I use?
Use INNER when you only want matching rows and LEFT when you must keep every row from the main table.
Why do I get duplicate rows?
One row on the left matches several rows on the right, so it appears once per match. That is correct behaviour; group or use DISTINCT if you need one row.
Learn it step by step
Practise this, step by step
Short lessons with quizzes, practice and interview questions, from your first program to job-ready.