LEFT JOIN

Introduction

LEFT JOIN returns all rows from the left table, plus matching rows from the right (NULL where none match). Use it whenever you want every row from one side, regardless of matches.

Understanding the topic

Core concepts to understand:

• FROM a LEFT JOIN b ON ....
• All A rows kept; B columns NULL when no match.
• Find missing rows: WHERE b.id IS NULL after LEFT JOIN.
• Common for reports — show users even if they have zero orders.
• Index the right-side join column for speed.

Syntax reference

Visual workflow / architecture:

LEFT JOIN  users  ──◀──  orders
 user 1: ✓ matches → rows
 user 2: ✓ matches → rows
 user 3: ✗ no orders → row with NULLs

SELECT <cols>
FROM   <a> [AS a]
LEFT JOIN <b> [AS b]
       ON  <a.key> = <b.key>;

SELECT u.name, o.amount
FROM   users  u
LEFT JOIN orders o ON o.user_id = u.id;

Real-world use

Dashboard 'all users + their order count (0 if none)' is the canonical LEFT JOIN. Same for products with no sales, devices with no events.

Best practices

• Use LEFT JOIN when the left table is the source of truth.
• Be careful — adding a WHERE on right-side columns can silently turn it into INNER.
• Index the right-side join column.

Common mistakes

• Filtering right-side columns in WHERE breaks the LEFT JOIN semantic.
• COUNT(*) over LEFT JOIN counts NULL rows — use COUNT(b.id).

Hands-on exercise

Interview preparation — practice these questions:

• Q1. LEFT JOIN vs INNER JOIN.
• Q2. How to find rows in A with no match in B.
• Q3. Why filtering on B in WHERE can break it.
• Q4. COUNT(*) vs COUNT(b.id) on a LEFT JOIN.
• Q5. Real example you've used.