Difference between INNER JOIN and LEFT JOIN

The core difference between INNER JOIN and LEFT JOIN (LEFT OUTER JOIN) lies in how they handle rows that have no matching records in the joined table:

🟢 Junior Level

The core difference between INNER JOIN and LEFT JOIN (LEFT OUTER JOIN) lies in how they handle rows that have no matching records in the joined table:

INNER JOIN: Returns only rows that have matching records in both tables. If a row in Table A has no counterpart in Table B, it is discarded.
LEFT JOIN: Returns all rows from the left table, regardless of whether a match exists in the right table. For rows without a match, columns from the right table are filled with NULL.

Concrete Example

Imagine an e-commerce platform with a customers table and an orders table:

Alice (id=1): Has placed 2 orders.
Bob (id=2): Has placed 1 order.
Charlie (id=3): Has placed 0 orders.

-- 1. INNER JOIN: Returns only customers who have placed orders
SELECT c.name, o.order_id, o.total_amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- Result:
-- Alice   | 101 | 50.00
-- Alice   | 102 | 75.00
-- Bob     | 103 | 120.00
-- (Charlie is discarded because he has no matching orders)

-- 2. LEFT JOIN: Returns ALL customers, preserving Charlie with NULLs
SELECT c.name, o.order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- Result:
-- Alice   | 101  | 50.00
-- Alice   | 102  | 75.00
-- Bob     | 103  | 120.00
-- Charlie | NULL | NULL

When to Use Which:

INNER JOIN: When both entities are mandatory to satisfy the business logic (e.g., “Show all shipped packages and their assigned courier”).
LEFT JOIN: When you must preserve the complete master list regardless of whether child records exist (e.g., “Show all registered users and their last login date, including users who have never logged in”).

🟡 Middle Level

Optimizer Freedom & Join Reordering

The relational algebra properties of INNER JOIN and LEFT JOIN dictate how freely the query optimizer can plan the query:

1. INNER JOIN: Fully Associative & Commutative

$(A \bowtie B) \bowtie C \equiv A \bowtie (B \bowtie C) \equiv (A \bowtie C) \bowtie B$ The query optimizer has total freedom to evaluate all $N!$ join permutations. It will reorder tables to join the two most selective tables first, dramatically reducing the size of intermediate result sets.

2. LEFT JOIN: Non-Associative

$(A \leftouterjoin B) \leftouterjoin C \not\equiv A \leftouterjoin (B \leftouterjoin C)$ The optimizer’s reordering freedom is strictly constrained. In a chain of LEFT JOINs, the leftmost relation must serve as the driving outer stream, locking the engine into a specific evaluation hierarchy.

The `WHERE` Clause Trap (Accidental Inner Join)

Placing a filter condition for the right-hand table in the WHERE clause instead of the ON clause silently destroys the LEFT JOIN semantics:

-- ❌ ACCIDENTAL INNER JOIN:
SELECT c.name, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.total_amount > 100;

Why did this become an INNER JOIN?

For customers with zero orders (like Charlie), o.total_amount is NULL.
In SQL Three-Valued Logic, evaluating NULL > 100 yields UNKNOWN (falsy).
Because the WHERE clause rejects any row that does not evaluate strictly to TRUE, Charlie is discarded from the result!

-- ✅ CORRECT: Placing the condition in the ON clause preserves unmatched customers
SELECT c.name, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.total_amount > 100;
-- Charlie is preserved with NULL total_amount!

Common Aggregation Pitfalls with LEFT JOIN

-- ❌ WRONG: COUNT(*) counts the row containing NULLs, returning 1 for Charlie!
SELECT c.name, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- ✅ CORRECT: COUNT(o.order_id) ignores NULLs, returning 0 for Charlie
SELECT 
    c.name, 
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

🔴 Senior Level

Query Optimizer Rewrites: Outer Join Removal (PostgreSQL 10+)

PostgreSQL features an optimization called Outer Join Removal. If a query contains a LEFT JOIN, but:

No columns from the right-hand table appear in SELECT, WHERE, GROUP BY, or ORDER BY.
The join condition connects to a column in the right table that is guaranteed unique (PRIMARY KEY or UNIQUE NOT NULL).

Then the query planner completely removes the right table from the physical execution plan:

-- Left table: customers, Right table: customer_profiles (customer_id is PRIMARY KEY)
SELECT c.id, c.name 
FROM customers c
LEFT JOIN customer_profiles p ON c.id = p.customer_id;

-- EXPLAIN (ANALYZE) output:
-- Seq Scan on customers c  <-- customer_profiles is 100% eliminated from the plan!

[!WARNING] Outer Join Removal is disabled if the unique constraint is declared as DEFERRABLE. Because a deferred constraint permits temporary duplicates within a transaction prior to commit, the optimizer cannot prove at execution time that the join cardinality is strictly 1:1 or 1:0.

Automatic Outer-to-Inner Transformation

When PostgreSQL detects a null-rejecting predicate in the WHERE clause against the right table of a LEFT JOIN, it formally rewrites the parse tree into an INNER JOIN:

EXPLAIN (VERBOSE)
SELECT c.name, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'COMPLETED';
-- The output displays: "Join Type: Inner"

By recognizing that o.status = 'COMPLETED' will discard any NULL row, the planner rewrites the query as an INNER JOIN. This unlocks full commutativity, allowing the optimizer to reorder tables and swap hash table build/probe roles.

Anti-Join Optimization Pattern

To find records in Table A that do not exist in Table B, using a LEFT JOIN ... WHERE right.id IS NULL compiles directly into an optimized Hash Anti-Join or Merge Anti-Join:

-- Finds customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;
-- Compiles to Hash Anti-Join: probes the hash table and emits row ONLY if no match is found

Performance Comparison Matrix

Aspect	`INNER JOIN`	`LEFT JOIN`
Search Space Complexity	High ($N!$ permutations, finding the optimal join tree)	Constrained (left table must drive execution)
Intermediate Memory Footprint	Small (unmatched rows discarded early)	Large (must preserve all left-side rows)
Chaining 5+ Tables	Scalable; optimizer joins selective tables first	Risky; can force expensive Nested Loop bottlenecks

4 Tricky Questions

1. Under what exact conditions will PostgreSQL automatically rewrite a `LEFT JOIN` into an `INNER JOIN`?

Answer:
When the WHERE clause contains a null-rejecting predicate applied to any column of the right-hand table.
A predicate is “null-rejecting” if evaluating it against NULL returns FALSE or UNKNOWN (e.g., WHERE right.status = 'ACTIVE', WHERE right.amount > 0, or WHERE right.created_at IS NOT NULL).
Because any unmatched row produced by the LEFT JOIN contains NULL for right-table columns, these rows are guaranteed to be filtered out by the WHERE clause. The optimizer detects this at compile time, rewrites the join as an INNER JOIN, and gains full freedom to reorder the join tree and build hash tables from whichever side is smaller.

2. Why does `COUNT(*)` versus `COUNT(right_table.id)` produce catastrophic reporting bugs in queries with a `LEFT JOIN`?

Answer:

COUNT(*) counts the total number of physical rows produced by the join pipeline, regardless of whether column values are NULL. If a customer has zero orders, a LEFT JOIN still produces 1 row for that customer (with NULL order fields). COUNT(*) counts that row, falsely reporting that the customer has 1 order!
COUNT(right_table.order_id) evaluates the specific expression and strictly counts non-NULL values. For customers with no orders, order_id is NULL, so COUNT(o.order_id) correctly returns 0.

3. Why does Outer Join Removal fail if the unique constraint on the right table is declared as `DEFERRABLE`?

Answer:
PostgreSQL can only eliminate a LEFT JOIN if it is mathematically guaranteed that joining the right table will neither multiply rows (guaranteed by uniqueness) nor filter rows (guaranteed by LEFT JOIN).
If the unique constraint is marked DEFERRABLE (e.g., UNIQUE (user_id) DEFERRABLE INITIALLY DEFERRED), a concurrent transaction can temporarily insert duplicate user_id values, postponing the uniqueness check until commit time. Because the optimizer cannot guarantee at runtime that the join will produce at most 1 row per left tuple, it cannot safely remove the join.

4. Why is chaining 5+ `LEFT JOIN`s significantly more dangerous for query performance than chaining 5+ `INNER JOIN`s?

Answer:
Because INNER JOIN is associative and commutative, allowing the cost-based optimizer to evaluate all permutations and perform the most restrictive filters first, drastically shrinking intermediate row counts.
LEFT JOIN is non-associative. In a query with 5 cascading LEFT JOINs, the engine is forced to evaluate tables in strict syntactic left-to-right order. If the leading left table contains 10,000,000 rows, every subsequent join must process all 10,000,000 rows throughout the entire pipeline, preventing the optimizer from shrinking intermediate data and frequently degenerating into hours of disk-spilling Nested Loops.

🎯 Interview Cheat Sheet

Core Concepts

INNER JOIN: Preserves records matching both sides; discards unmatched rows.
LEFT JOIN: Preserves all records from the left relation; fills right-side non-matches with NULL.
Join Reordering: INNER JOIN is commutative and associative ($A \bowtie B \equiv B \bowtie A$); LEFT JOIN restricts planner ordering.
ON vs WHERE: Right-table filters in WHERE turn a LEFT JOIN into an INNER JOIN. Put right-table conditions in ON.

Key Optimizer Behaviors

Outer Join Removal: Postgres strips unreferenced, uniquely constrained LEFT JOIN tables out of the physical execution plan.
Outer-to-Inner Rewrite: A null-rejecting predicate on the right table in WHERE automatically converts LEFT JOIN into INNER JOIN.
Anti-Join Pattern: LEFT JOIN ... WHERE right.id IS NULL compiles directly into an optimized Hash Anti-Join or Merge Anti-Join.

Aggregation Rules

Count: Always use COUNT(right.id) instead of COUNT(*) to ensure non-matching rows return 0 instead of 1.
Sum: Always wrap aggregates in COALESCE(SUM(right.amount), 0) to handle non-matches without returning NULL.

Red Flags (What NOT to Say)

❌ “I always use LEFT JOIN just in case so I don’t lose rows.” (LEFT JOIN restricts optimizer reordering and consumes significantly more memory).
❌ “Putting a condition in ON vs WHERE produces the exact same result in a LEFT JOIN.” (Putting it in WHERE filters out rows with NULL, turning it into an INNER JOIN).
❌ “SUM() on a LEFT JOIN automatically returns 0 if there are no matches.” (It returns NULL; wrap in COALESCE).

What JOIN types exist — Physical join strategies (Nested Loop, Hash, Merge)
What is better JOIN or subquery — Anti-joins vs NOT EXISTS
Difference between WHERE and HAVING — Filter placement in SQL pipelines
What is Explain plan — Verifying Join Type in EXPLAIN output