Chapter 33: PostgreSQL LEFT JOIN
PostgreSQL LEFT JOIN ☕🛡️
Last time we covered INNER JOIN — which only shows matching rows from both tables. Today we go to LEFT JOIN — which says:
“I want every single row from the left table, no matter what. If there is a matching row in the right table → great, show it. If there is no match → still show the left row, but put NULL in the right table’s columns.”
This is extremely common because in real life you almost always want to see all records from your main entity (customers, students, products, orders…) even if some of them don’t have related data yet.
1. What LEFT JOIN really does (teacher drawing on imaginary board)
Imagine two circles:
- Left circle = students (the main table you care about)
- Right circle = enrollments (optional related data)
INNER JOIN → only the overlap (students who enrolled) LEFT JOIN → entire left circle + overlap → Students with no enrollments still appear → with NULLs in enrollment columns
Official PostgreSQL wording (from docs):
LEFT OUTER JOIN returns all rows from the left table and the matched rows from the right table. The result is NULL from the right side if there is no match.
(Note: people usually just say LEFT JOIN — the word OUTER is optional and almost never written)
2. Basic syntax (the shape you will type 1,000 times)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT left_table.column1, right_table.column2, ... FROM left_table LEFT JOIN right_table ON left_table.foreign_key = right_table.primary_key [WHERE …] [ORDER BY …]; |
- The table written first (after FROM) is the left table
- Everything from left table is kept
- Matching rows from right table are added
- No match → right columns = NULL
3. Real, detailed example – using our college schema
Tables (same as last time):
students (left table – we want all students)
| id | first_name | last_name | gpa | |
|---|---|---|---|---|
| 1 | Aarav | Patel | aarav@hyd.edu | 8.95 |
| 2 | Priya | Reddy | priya@gmail.com | 9.60 |
| 3 | Rahul | Sharma | rahul@yahoo.com | 7.85 |
| 4 | Sneha | Kumar | sneha@gmail.com | 9.20 |
| 5 | Vikram | Naik | vikram@hyd.edu | 8.40 |
enrollments (right table – optional)
| id | student_id | course_id | grade | enrolled_at |
|---|---|---|---|---|
| 1 | 1 | 1 | A | 2025-08-01 |
| 2 | 1 | 2 | A+ | 2025-08-02 |
| 3 | 2 | 1 | B+ | 2025-08-01 |
| 4 | 4 | 3 | A | 2025-08-05 |
| 5 | 5 | 2 | B | 2025-08-10 |
(Rahul id=3 has no enrollment)
Example 1: Basic LEFT JOIN – all students + their enrollments
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT s.first_name || ' ' || s.last_name AS student_name, e.course_id, e.grade, e.enrolled_at FROM students s LEFT JOIN enrollments e ON s.id = e.student_id ORDER BY student_name; |
Result:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
student_name | course_id | grade | enrolled_at ---------------|-----------|-------|--------------------- Aarav Patel | 1 | A | 2025-08-01 Aarav Patel | 2 | A+ | 2025-08-02 Priya Reddy | 1 | B+ | 2025-08-01 Rahul Sharma | NULL | NULL | NULL Sneha Kumar | 3 | A | 2025-08-05 Vikram Naik | 2 | B | 2025-08-10 |
→ Rahul appears! → but with NULLs in enrollment columns → This is the classic use-case of LEFT JOIN
Example 2: LEFT JOIN + multiple tables + COALESCE (very real pattern)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT s.first_name || ' ' || s.last_name AS student, c.name AS course_name, COALESCE(e.grade, 'Not enrolled') AS grade_status, COALESCE(e.enrolled_at::text, '-') AS enrollment_date FROM students s LEFT JOIN enrollments e ON s.id = e.student_id LEFT JOIN courses c ON c.id = e.course_id ORDER BY student, course_name; |
Result (partial):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
student | course_name | grade_status | enrollment_date --------------|---------------------------------|----------------|----------------- Aarav Patel | Database Systems | A+ | 2025-08-02 Aarav Patel | Introduction to Programming | A | 2025-08-01 Priya Reddy | Introduction to Programming | B+ | 2025-08-01 Rahul Sharma | NULL | Not enrolled | - Sneha Kumar | Machine Learning Basics | A | 2025-08-05 Vikram Naik | Database Systems | B | 2025-08-10 |
→ COALESCE turns ugly NULLs into nice readable text
Example 3: Find students with no enrollments (very common report)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT s.first_name || ' ' || s.last_name AS student_name, s.email FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.student_id IS NULL -- magic condition! ORDER BY student_name; |
Result:
|
0 1 2 3 4 5 6 7 8 |
student_name | email ---------------|------------------- Rahul Sharma | rahul@yahoo.com |
→ This is the classic way to find “orphaned” / “non-related” records → Very important interview question pattern
4. LEFT JOIN vs INNER JOIN – quick comparison (same data)
| Query type | Rows returned | Rahul included? | Use case feeling |
|---|---|---|---|
| INNER JOIN | 5 rows | No | “Only enrolled students” |
| LEFT JOIN | 6 rows | Yes (with NULLs) | “All students — show enrollment if exists” |
5. Best practices & common mistakes (2026 production tips)
| Mistake / Question | Problem / Surprise | Fix / Recommendation |
|---|---|---|
| Forget WHERE … IS NULL | No rows for non-matches | Use WHERE right.id IS NULL for non-matching rows |
| Use LEFT when you really want INNER | Too many rows with NULLs | Choose join type based on business question |
| LEFT JOIN on wrong column | All rows appear with NULLs | Double-check ON condition (fk = pk) |
| Performance on large tables | Slow if no index on join columns | Index foreign key columns (almost always) |
| LEFT JOIN + WHERE on right table column | Turns into INNER JOIN! | Move right-table conditions to ON clause or use COALESCE |
Dangerous pattern (very common mistake):
|
0 1 2 3 4 5 6 7 8 |
FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.grade = 'A' -- ← this kills the LEFT ! |
→ Becomes equivalent to INNER JOIN → Fix: move to ON … AND e.grade = ‘A’ or use COALESCE
Your mini homework right now
- Write a LEFT JOIN query showing all students + their grades (use COALESCE for ‘Not enrolled’)
- Modify it to show only students with no enrollments (WHERE … IS NULL)
- Add a third LEFT JOIN to also show course names
- Try the dangerous mistake above → see how Rahul disappears
Next class?
Tell me:
- Want to compare LEFT JOIN vs RIGHT JOIN vs FULL OUTER JOIN with same data?
- LEFT JOIN with multiple tables (3–4 way join)?
- LEFT JOIN + aggregate (COUNT, SUM, AVG) – e.g. students + number of courses?
- Performance differences INNER vs LEFT + index usage?
Your guru is ready — what’s the next topic? 🚀
