How does left join work?

General Questions~4 min read

LEFT JOIN returns every row from the left table, plus matching rows from the right table. Where no match exists, the right-side columns come back as NULL.

Theory

TL;DR

Think of the left table as a guest list. Everyone on it appears in the result. The right table is the food order. No order means an empty plate (NULL).
Every row from the left table appears at least once. Non-matching right rows get dropped entirely.
Use LEFT JOIN when you need all left records regardless of whether a match exists on the right side.

Quick example

sql

-- clients (left table)
-- client_id | name
-- 1         | Alice
-- 2         | Bob
-- 3         | Carol

-- purchases (right table)
-- purchase_id | client_id | item
-- 1           | 1         | Laptop
-- 2           | 1         | Mouse
-- Bob and Carol have no purchases

SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id;

-- Result:
-- Alice | Laptop
-- Alice | Mouse
-- Bob   | NULL
-- Carol | NULL

All three clients appear. Bob and Carol show NULL for item because they have no matching rows in purchases.

Key difference from INNER JOIN

INNER JOIN only returns rows where both tables have a match. Bob and Carol would disappear from the result completely. LEFT JOIN keeps them and fills the right-side columns with NULL. That single behavior is the whole reason LEFT JOIN exists.

When to use

All customers plus their orders, including those with zero orders: LEFT JOIN.
Only customers who placed at least one order: INNER JOIN.
Full merge where both sides may have missing data: FULL OUTER JOIN.
Right table prioritized: RIGHT JOIN, but most teams just swap the table order and write LEFT JOIN instead.

How it works internally

The engine scans every row in the left table. For each left row, it looks for matching rows in the right table using the ON condition. If it finds matches, it outputs one combined row per match. If it finds nothing, it outputs the left row once with NULLs in all right columns.

Indexes on the join key (like client_id in purchases) make this fast. Without an index on a large table, the engine compares every left row against every right row. That is the most common reason LEFT JOINs kill performance in production.

Common mistakes

Mistake 1: WHERE on a right-table column converts LEFT JOIN into INNER JOIN.

sql

-- BUG: Bob and Carol disappear
SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id
WHERE p.item IS NOT NULL;

-- FIX: move the condition into ON
SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p
  ON c.client_id = p.client_id AND p.item IS NOT NULL;

WHERE runs after the join and removes the NULL rows you wanted to keep. Putting the condition in ON changes when the filter applies, so unmatched left rows still make it through.

Mistake 2: Multiple right-side matches multiply row count.

If one client has 50 orders, you get 50 rows for that client. Correct behavior, but it surprises people who expect one row per client. Use COUNT with GROUP BY instead.

sql

SELECT c.name, COUNT(p.purchase_id) AS total_purchases
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id
GROUP BY c.client_id, c.name;
-- Alice | 2
-- Bob   | 0
-- Carol | 0

Mistake 3: No index on the join key.

sql

CREATE INDEX ON purchases(client_id);

Without it, a LEFT JOIN on a million-row table does a full scan on every query. Add the index once and the difference is dramatic.

Real-world usage

E-commerce: all users LEFT JOIN orders, to find customers who never bought anything.
SaaS dashboards: all users LEFT JOIN events, so zero-activity users still appear in retention reports.
CRM: all leads LEFT JOIN deals, so the pipeline shows leads with no deal attached.
REST API: users LEFT JOIN profiles when a user profile is optional and not every user has one.

Follow-up questions

Q: Can LEFT JOIN return duplicate rows?
A: Yes. If the right table has multiple matching rows, the left row repeats once per match. Use GROUP BY or aggregate functions to collapse them.

Q: What if the left table itself is empty?
A: The result is empty. There are no left rows to preserve.

Q: Is LEFT JOIN slower than INNER JOIN?
A: Often slightly, because the engine tracks unmatched left rows. With proper indexes the gap is small. The bigger factor is result set size and whether you aggregate afterward.

Q: You have a 3-table chain: users LEFT JOIN orders LEFT JOIN items. You add WHERE items.name = 'Laptop'. What happens?
A: The entire result behaves like INNER JOIN. Users with no orders and orders with no matching items get dropped. Move the condition to the ON clause of the second join: ON orders.order_id = items.order_id AND items.name = 'Laptop'.

Examples

Basic: all clients with optional purchases

sql

-- Returns all clients; NULL where no purchase exists
SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id;

-- Alice | Laptop
-- Alice | Mouse
-- Bob   | NULL
-- Carol | NULL

Alice appears twice because she has two purchases. Bob and Carol appear once each with NULL in item.

Intermediate: order stats for a user dashboard

sql

-- users: user_id | username
-- orders: order_id | user_id | total

SELECT
  u.username,
  COUNT(o.order_id)        AS order_count,
  COALESCE(AVG(o.total), 0) AS avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;

-- alice | 2 | 524.00
-- bob   | 0 | 0.00

COUNT(o.order_id) returns 0 for bob because it counts non-NULL values only. COALESCE(..., 0) converts the NULL average to 0. This pattern shows up on almost every analytics dashboard that tracks per-user activity.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?