What is LEFT JOIN?
INNER JOIN vs LEFT JOIN
| JOIN Type | Shows | Example Result |
|---|---|---|
| INNER JOIN | Only matches | John, Mary (2 rows) |
| LEFT JOIN | All from left + matches | John, Mary, Peter (3 rows) |
2 rows
LEFT JOIN shows ALL rows from the left table, even if there is no match in the right table.
Simple analogy: Show all students and their classes. If a student has no class, still show the student (with empty class).
Basic Example
INNER JOIN vs LEFT JOIN
| JOIN Type | Shows | Example Result |
|---|---|---|
| INNER JOIN | Only matches | John, Mary (2 rows) |
| LEFT JOIN | All from left + matches | John, Mary, Peter (3 rows) |
2 rows
Same tables as before:
Students: John, Mary, Peter Enrollments: John in Math, Mary in Science
SELECT students.name, enrollments.course
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;Result:
- John - Math
- Mary - Science
- Peter - NULL (no course, but Peter is shown)
Difference from INNER JOIN
INNER JOIN vs LEFT JOIN
| JOIN Type | Shows | Example Result |
|---|---|---|
| INNER JOIN | Only matches | John, Mary (2 rows) |
| LEFT JOIN | All from left + matches | John, Mary, Peter (3 rows) |
2 rows
INNER JOIN: Shows only matches (John, Mary) LEFT JOIN: Shows all from left table (John, Mary, Peter)
Real-Life Example
INNER JOIN vs LEFT JOIN
| JOIN Type | Shows | Example Result |
|---|---|---|
| INNER JOIN | Only matches | John, Mary (2 rows) |
| LEFT JOIN | All from left + matches | John, Mary, Peter (3 rows) |
2 rows
Find all customers and their orders (including customers who never ordered):
SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;Shows all customers, even those with no orders.
When to Use
INNER JOIN vs LEFT JOIN
| JOIN Type | Shows | Example Result |
|---|---|---|
| INNER JOIN | Only matches | John, Mary (2 rows) |
| LEFT JOIN | All from left + matches | John, Mary, Peter (3 rows) |
2 rows
Use LEFT JOIN when you want to keep ALL records from the first table.
Example: List all products and their sales (including products never sold)
What Comes Next
INNER JOIN vs LEFT JOIN
| JOIN Type | Shows | Example Result |
|---|---|---|
| INNER JOIN | Only matches | John, Mary (2 rows) |
| LEFT JOIN | All from left + matches | John, Mary, Peter (3 rows) |
2 rows
Next: RIGHT JOIN (opposite of LEFT JOIN)