Joins (Deep Dive)
LEFT, RIGHT, and FULL JOINS
Preserving unmatched rows from one or both sides of a join.
Interview: Outer joins are a frequent interview topic — interviewers test whether you understand which side is preserved, how NULLs appear for unmatched rows, how to filter "orphans" with IS NULL, and the behavioral difference between LEFT JOIN and INNER JOIN when rows have no match.
Outer joins preserve rows that have no match in the other table, filling in NULL for the missing side. Unlike INNER JOIN (which discards unmatched rows), outer joins let you answer questions like "which customers have never placed an order?" or "which products have never been reviewed?". PostgreSQL supports three outer join types: LEFT, RIGHT, and FULL.
1. Introduction
Outer joins preserve unmatched rows from one or both sides of a join. LEFT JOIN keeps all left-table rows (filling NULL for unmatched right-side columns). RIGHT JOIN keeps all right-table rows. FULL OUTER JOIN keeps all rows from both sides. They're essential for finding "orphans" — records with no related data — and for ensuring report completeness.
2. Why It Matters
- Orphan detection: "Which customers have never ordered?" requires LEFT JOIN + IS NULL.
- Report completeness: All departments must appear in the report, even those with zero activity.
- WHERE trap: Filtering the optional side in WHERE silently converts LEFT JOIN to INNER JOIN — the #1 outer join bug.
- Data reconciliation: FULL OUTER JOIN finds records existing in one system but not another.
3. Real-World Analogy
Outer joins are like a school attendance check. LEFT JOIN is the teacher reading from the roster (left table) and checking who showed up (right table). Every student on the roster appears — those absent get "NULL" for attendance status. RIGHT JOIN would be reading from the sign-in sheet and noting who signed in that isn't on the roster (substitute teachers). FULL OUTER JOIN captures everyone: present students, absent students, and unexpected visitors.
4. How It Works
| Join Type | Left No Match | Right No Match | Both Match |
|---|---|---|---|
| INNER | Excluded | Excluded | Included |
| LEFT | Kept (right=NULL) | Excluded | Included |
| RIGHT | Excluded | Kept (left=NULL) | Included |
| FULL | Kept (right=NULL) | Kept (left=NULL) | Included |
- LEFT JOIN is the most commonly used outer join. Use it to keep all rows from the "primary" table.
- RIGHT JOIN = LEFT JOIN with tables swapped. Rarely used — most devs prefer LEFT JOIN for readability.
- FULL OUTER JOIN keeps everything. Often combined with
WHERE a.id IS NULL OR b.id IS NULLto find only mismatches.
5. Internal Architecture
6. Visual Explanation
7. Practical Example
8. Common Mistakes
- WHERE on the optional side:
LEFT JOIN ... WHERE orders.total > 100silently converts to INNER JOIN. Move the condition to ON. - COUNT(*) vs COUNT(right.id): With LEFT JOIN, COUNT(*) counts the preserved row (1), while COUNT(right.id) correctly returns 0 for unmatched rows.
- Missing COALESCE: LEFT JOIN produces NULLs for unmatched rows — always use COALESCE for display values.
Interview Insight
"Find customers who never ordered." Classic anti-join: LEFT JOIN ... WHERE orders.id IS NULL. Is it the same as NOT EXISTS? In PostgreSQL, both optimize identically, but NOT EXISTS is more readable for complex conditions.
Common Pitfall
WHERE negating outer join: LEFT JOIN ... WHERE orders.total > 100 → NULL > 100 is UNKNOWN → row excluded. Fix: put in ON clause or add OR orders.id IS NULL.
9. Quick Quiz
Q1: What does A LEFT JOIN B WHERE B.col = 'x' effectively become?
A) LEFT JOIN B) INNER JOIN C) FULL JOIN
Answer: B — WHERE on the optional side excludes NULL rows, converting it to INNER JOIN.
Q2: With LEFT JOIN, what does COUNT(*) return for a row with no match?
A) 0 B) 1 C) NULL
Answer: B — COUNT(*) counts the preserved row (with NULLs) as 1. Use COUNT(right.id) for 0.
10. Scenario-Based Challenge
Build a Customer Activity Report
Show ALL customers with: order count, total spent, and last order date. Include customers who never ordered (show 0 and 'Never'). Then flag customers as 'Active' (ordered in last 90 days), 'Inactive' (ordered but not recently), or 'Prospect' (never ordered). Use LEFT JOIN, COALESCE, and CASE.
11. Debugging Exercise
This query should show all products including those without reviews, but some products are missing. Why?
12. Interview Questions
Q: How do you find records with no related data?
A: LEFT JOIN + WHERE right.id IS NULL (anti-join pattern). Or use NOT EXISTS — both optimize identically in PostgreSQL. NOT EXISTS is preferred for complex conditions.
Q: What's the difference between LEFT JOIN and RIGHT JOIN?
A: LEFT JOIN preserves the left table's unmatched rows. RIGHT JOIN preserves the right table's. A RIGHT JOIN B = B LEFT JOIN A. LEFT JOIN is preferred for readability.
Q: When would you use FULL OUTER JOIN?
A: Data reconciliation — finding records in system A that aren't in system B and vice versa. Combine with WHERE a.id IS NULL OR b.id IS NULL to show only mismatches.
13. Production Considerations
- ON vs WHERE: For the optional side, put filters in ON (not WHERE) to preserve outer join behavior. This is the #1 outer join bug in production code.
- COALESCE everywhere: Always wrap optional-side columns in COALESCE for display. NULL values in reports confuse users and break calculations.
- FULL JOIN on large tables: Can produce very large result sets if both sides have many unmatched rows. Always estimate result size before running.
- NOT EXISTS vs LEFT JOIN IS NULL: Both work identically in PostgreSQL. NOT EXISTS is more readable and supports complex conditions better.
Use Cases
Customer analysis — finding all customers including those with no orders, purchases, or activity
Orphan detection — finding records with no related data (unassigned employees, unreviewed products, unpaid invoices)
Data reconciliation — comparing records between two systems to find mismatches using FULL OUTER JOIN
Optional relationships — displaying entities with optional related data (users with optional profiles, products with optional reviews)
Reporting completeness — ensuring all categories, departments, or time periods appear in reports even when they have zero activity
Common Mistakes
Filtering the optional side in WHERE instead of ON — this silently converts LEFT JOIN to INNER JOIN, excluding the very rows you wanted to preserve
Using COUNT(*) instead of COUNT(right_table.id) with LEFT JOIN — COUNT(*) counts the preserved row (with NULLs) as 1, while COUNT(right_table.id) correctly returns 0
Forgetting COALESCE for display — LEFT JOIN produces NULLs for unmatched rows; use COALESCE(column, default_value) for user-friendly output
Using RIGHT JOIN when LEFT JOIN is clearer — RIGHT JOIN is never necessary; you can always rewrite it as LEFT JOIN with tables swapped
FULL OUTER JOIN producing too many NULLs — when both sides have many unmatched rows, the result is hard to interpret; always add COALESCE on join keys