LEFT, RIGHT, and FULL JOINS

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

• 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 NULL to find only mismatches.

5. Internal Architecture

The WHERE Trap (most common outer join bug):
-- INTENDED: All customers, with their completed orders
  SELECT c.name, o.total
  FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
  WHERE o.status = 'completed';  ← BUG!
-- o.status is NULL for customers without orders
  -- NULL = 'completed' → UNKNOWN → row excluded
  -- Result: same as INNER JOIN (orphans are gone!)
-- FIX: Put filter in ON clause
  SELECT c.name, o.total
  FROM customers c LEFT JOIN orders o
    ON o.customer_id = c.id AND o.status = 'completed';
-- Now: all customers appear; order columns are NULL
  -- for those without completed orders
Execution:
  1. Perform join (match rows)
  2. For unmatched preserved-side rows: add NULL-filled row
  3. Apply WHERE filter (THIS is where the trap occurs)
  4. Result: only rows surviving WHERE

6. Visual Explanation

7. Practical Example

-- All customers with order count (including 0)
SELECT c.id, c.name, c.email,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC;
-- Anti-join: customers who NEVER ordered
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- Same with NOT EXISTS (preferred for complex conditions)
SELECT c.id, c.name, c.email
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- LEFT JOIN with filter in ON (not WHERE)
SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'completed';
-- FULL OUTER JOIN: data reconciliation
SELECT COALESCE(a.email, b.email) AS email,
  CASE WHEN a.email IS NOT NULL AND b.email IS NOT NULL THEN 'BOTH'
       WHEN a.email IS NOT NULL THEN 'CRM_ONLY'
       ELSE 'BILLING_ONLY' END AS source
FROM crm_users a
FULL OUTER JOIN billing_users b ON a.email = b.email
WHERE a.email IS NULL OR b.email IS NULL;

8. Common Mistakes

• WHERE on the optional side: LEFT JOIN ... WHERE orders.total > 100 silently 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.

9. Quick Quiz

10. Scenario-Based Challenge

11. Debugging Exercise

This query should show all products including those without reviews, but some products are missing. Why?

-- BUG: Products without reviews are excluded
SELECT p.name, AVG(r.rating) AS avg_rating
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
WHERE r.created_at >= NOW() - INTERVAL '1 year'
GROUP BY p.id, p.name;
-- r.created_at is NULL for products without reviews
-- NULL >= ... → UNKNOWN → row excluded (becomes INNER JOIN)
-- FIX: Move date filter to ON clause
SELECT p.name, AVG(r.rating) AS avg_rating
FROM products p
LEFT JOIN reviews r
  ON r.product_id = p.id
  AND r.created_at >= NOW() - INTERVAL '1 year'
GROUP BY p.id, p.name;

12. Interview Questions

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.