INNER JOIN returns only the rows where matching values exist in both tables.
- If there is no match, that row is ignored.
- Think: “Show me only common records.”
LEFT JOIN returns all rows from the left table, and the matching rows from the right table.
- If there’s no match in the right table, it will show
NULL. - Think: “Show me everything from the left side, and add details from the right side if available.”
Example
Let’s say we have two tables:
students
| student_id | name |
|---|---|
| 1 | Ramesh |
| 2 | Priya |
| 3 | Amit |
marks
| mark_id | student_id | subject | marks |
|---|---|---|---|
| 1 | 1 | Maths | 90 |
| 2 | 2 | Science | 85 |
INNER JOIN Example
sql
SELECT students.name, marks.subject, marks.marks
FROM students
INNER JOIN marks
ON students.student_id = marks.student_id;
Result:
| name | subject | marks |
|---|---|---|
| Ramesh | Maths | 90 |
| Priya | Science | 85 |
- Only students who have marks appear.
- Amit is missing because he has no marks record.
LEFT JOIN Example
sql
SELECT students.name, marks.subject, marks.marks
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id;
Result:
| name | subject | marks |
|---|---|---|
| Ramesh | Maths | 90 |
| Priya | Science | 85 |
| Amit | NULL | NULL |
- All students appear.
- If marks exist → show them.
- If not (like Amit) → show
NULL.
Key Difference Table
| Feature | INNER JOIN | LEFT JOIN |
|---|---|---|
| Matching Rows | Shows only rows with matches | Shows all rows from left table |
| Unmatched Rows | Ignored | Show as NULL |
| Use Case | When you need only common records | When you need all from left + matches |
Real-Life Analogy
- INNER JOIN → “Invite only couples where both husband AND wife come to the party.”
- LEFT JOIN → “Invite all husbands. If wife comes, show her; if not, leave it blank.”
Super Simple Memory Trick
INNER JOIN = only matches
LEFT JOIN = all from left + matches from right