0Pricing
SQL Interview Prep · Lesson

LEFT JOIN and Preserving Unmatched Rows

What LEFT JOIN keeps and how NULLs appear for non-matching rows.

LEFT JOIN and Preserving Unmatched Rows is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Interview Setup

An interviewer says: "List every customer and their orders, including customers who have never ordered." An INNER JOIN drops the customers with no orders, so it fails this requirement. The job goes to LEFT JOIN.

A LEFT JOIN keeps every row from the left (first-named) table, and attaches matching rows from the right table when they exist. This lesson builds the precise mental model interviewers expect.

What LEFT JOIN Guarantees

The core promise: every row from the left table appears in the output at least once, matched or not.

  • If a left row finds matches on the right, you get one output row per match.
  • If a left row finds no match, you still get one output row, with all right-table columns set to NULL.

This is why interviewers call it an outer join: rows that fall outside the match set are preserved instead of discarded.

The Two Tables

Throughout this lesson we use two simple tables. customers has every customer; orders records purchases and references a customer via customer_id.

Notice customer 3 (Carol) has no row in orders. That unmatched customer is exactly what a LEFT JOIN must preserve.

-- customers
-- id | name
--  1 | Alice
--  2 | Bob
--  3 | Carol

-- orders
-- id | customer_id | amount
-- 10 |     1       |  50
-- 11 |     1       |  75
-- 12 |     2       |  20

Writing the LEFT JOIN

The syntax mirrors an inner join, swapping INNER for LEFT (the word OUTER is optional: LEFT JOIN and LEFT OUTER JOIN are identical).

The table written before the keyword is the preserved left side. Order matters: customers LEFT JOIN orders keeps all customers, not all orders.

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id;

Reading the Result

Here is the output of that query. Alice matched twice, so she appears twice. Bob matched once. Carol matched nothing, so she still appears with NULL in the amount column.

That single Carol row with NULL is the signature of a LEFT JOIN. An interviewer watching you reason aloud wants to hear: "unmatched left rows survive with NULLs on the right."

-- name  | amount
-- Alice |  50
-- Alice |  75
-- Bob   |  20
-- Carol | NULL   <-- preserved, no match

Where the NULLs Come From

The NULL is not stored anywhere. The database synthesizes it because there is no right-table row to supply amount.

Every column drawn from the right table becomes NULL for an unmatched left row: o.id, o.customer_id, and o.amount are all NULL in Carol's row. This matters because o.customer_id being NULL does not mean the customer has no id, it means there was no matching order at all.

Counting Orders Per Customer

A common follow-up: "How many orders does each customer have, including zeros?" A LEFT JOIN plus COUNT nails it, but you must count the right-table column, not *.

COUNT(o.id) ignores NULLs, so Carol scores 0. COUNT(*) would wrongly return 1 for Carol because the synthesized NULL row still counts as a row.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
-- Alice | 2
-- Bob   | 1
-- Carol | 0

COUNT(*) vs COUNT(column) Trap

This is a classic interview gotcha. After a LEFT JOIN, the unmatched left row physically exists in the result with NULL right columns.

  • COUNT(*) counts that row, returning 1 for a customer with no orders.
  • COUNT(o.id) skips the NULL, correctly returning 0.

Always count a column that is NULL on the no-match side when you need true zeros.

-- WRONG: Carol shows 1
SELECT c.name, COUNT(*) AS n
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Summing With LEFT JOIN

Aggregates beyond COUNT behave gently with the synthesized NULLs. SUM ignores NULLs, so an unmatched customer yields NULL (not an error).

Interviewers often want zero instead of NULL for reporting. Wrap the aggregate in COALESCE to substitute a default.

SELECT c.name,
       COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
-- Alice | 125
-- Bob   |  20
-- Carol |   0

Left vs Right Table, Said Clearly

When an interviewer asks "which table is preserved?", answer with confidence: the one to the left of the JOIN keyword. Read it left-to-right.

A LEFT JOIN B = keep all of A. To keep all of B instead, either flip to B LEFT JOIN A or use a RIGHT JOIN. Most teams prefer LEFT JOIN exclusively for readability, simply reordering the tables.

A Mental Checklist

When you see a LEFT JOIN in an interview, run this checklist out loud:

  • Preserved side? The left table, all rows kept.
  • Unmatched rows? Appear once with NULLs from the right.
  • Counting? Use COUNT(right_col) for true zeros.
  • Summing? Wrap in COALESCE if you need 0, not NULL.

Verbalizing this checklist signals senior-level rigor.

Quick Check

Test your LEFT JOIN intuition on counting.

Recap

LEFT JOIN preserves every row of the left table. Matched left rows pair with their right matches; unmatched left rows survive once with NULL in all right-table columns.

  • The left table is the one before the JOIN keyword.
  • Synthesized NULLs flag the no-match rows.
  • COUNT(right_col) gives true zeros; COUNT(*) does not.
  • COALESCE(SUM(...), 0) turns NULL totals into zero.

This is the foundation for finding missing data and anti-joins in the lessons ahead.

Frequently asked questions

Is the “LEFT JOIN and Preserving Unmatched Rows” lesson free?

Yes — the full text of “LEFT JOIN and Preserving Unmatched Rows” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “LEFT JOIN and Preserving Unmatched Rows”?

What LEFT JOIN keeps and how NULLs appear for non-matching rows. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LEFT JOIN and Preserving Unmatched Rows” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. LEFT JOIN and Preserving Unmatched Rows
  2. RIGHT and FULL OUTER JOIN Semantics
  3. Finding Rows With No Match (Anti-Join)
  4. The WHERE-on-Outer-Join Trap
← Back to SQL Interview Prep