SQL Joins Explained: INNER, LEFT, RIGHT and FULL With Two Tiny Tables
Joins are the part of SQL where students stop trusting themselves. The fix is to use two tables small enough to hold in your head, and to run every join on the same pair.
The two tables
students
| id | name |
|---|---|
| 1 | Aisha |
| 2 | Rohan |
| 3 | Meera |
enrollments
| student_id | course |
|---|---|
| 1 | DBMS |
| 1 | OS |
| 2 | DBMS |
| 4 | CN |
Notice what we built in on purpose:
- Meera (id 3) has no enrollment.
- Enrollment for
student_id = 4has no matching student. - Aisha has two enrollments.
Those three oddities are what make the join types behave differently.
INNER JOIN: only the matches
SELECT s.name, e.course
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;
| name | course |
|---|---|
| Aisha | DBMS |
| Aisha | OS |
| Rohan | DBMS |
Meera is gone (no enrollment) and the student_id = 4 row is gone (no student). INNER JOIN keeps a row only when both sides match. Aisha appears twice because she matches twice.
LEFT JOIN: everything from the left table
SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;
| name | course |
|---|---|
| Aisha | DBMS |
| Aisha | OS |
| Rohan | DBMS |
| Meera | NULL |
All three students appear. Meera has no match, so her course is NULL. The enrollment for student 4 is still dropped, because it belongs to the right table and has no partner on the left.
Reading rule: "LEFT JOIN: keep every row of the left table, fill the right side with NULL when there is no match."
RIGHT JOIN: everything from the right table
SELECT s.name, e.course
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;
| name | course |
|---|---|
| Aisha | DBMS |
| Aisha | OS |
| Rohan | DBMS |
| NULL | CN |
Now every enrollment appears. The one for student 4 shows up with NULL for the name, and Meera, who is on the left, is the one that disappears.
A RIGHT JOIN is just a LEFT JOIN with the tables swapped. Most people write LEFT JOINs only and reorder the tables.
FULL OUTER JOIN: everything from both
SELECT s.name, e.course
FROM students s
FULL OUTER JOIN enrollments e ON s.id = e.student_id;
| name | course |
|---|---|
| Aisha | DBMS |
| Aisha | OS |
| Rohan | DBMS |
| Meera | NULL |
| NULL | CN |
Both oddities survive: Meera with no course, and the course with no student. (MySQL does not support FULL OUTER JOIN directly. The usual workaround is a LEFT JOIN combined with a RIGHT JOIN using UNION.)
The pattern that comes up in exams: "find the missing ones"
"List students who have not enrolled in any course."
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;
Result: Meera. LEFT JOIN keeps everyone, and the WHERE ... IS NULL filter then keeps only those who found no match. This one pattern is worth memorizing.
One-glance summary
| Join | Keeps | Unmatched rows show |
|---|---|---|
| INNER | Matches only | Never |
| LEFT | All of left | NULL on the right |
| RIGHT | All of right | NULL on the left |
| FULL OUTER | All of both | NULL on whichever side is missing |
Two mistakes to avoid
- Putting the filter in the wrong place. In a LEFT JOIN, a condition on the right table placed in
WHEREcan silently turn it into an INNER JOIN, becauseNULLrows fail the condition. Put it in theONclause if you want to keep unmatched left rows. - Forgetting the ON clause. A join without a condition produces a Cartesian product: every row paired with every row.
Sumantrak™ is an AI mentor built for Indian engineering, diploma, BCA and BSc CS/IT students — DBMS, SQL practicals, and the rest of your syllabus, explained like this. Free to start →