LEFT / RIGHT JOIN

Overview

A LEFT JOIN keeps EVERY row from the left table, and attaches matching rows from the right table — filling the right-hand columns with NULL when there is no match. An INNER JOIN would silently drop those unmatched left rows; LEFT JOIN will preserve them. Against the shared dataset, customers Greg and Hana placed no orders: SELECT c.name, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id ORDER BY c.id, o.id; Every customer appears; Greg and Hana come back with order_id = NULL. With INNER JOIN they would vanish entirely. A RIGHT JOIN is the mirror image — it keeps every row from the RIGHT table instead. RIGHT JOIN is rare in practice: you can always rewrite it as a LEFT JOIN by swapping the table order, which reads more naturally. Think of LEFT JOIN as 'keep all of the left, decorate with the right where it exists' — the left table is the spine, the right table is optional trim.

LEFT JOIN answers 'show me ALL X, with their Y if any' — all customers and their orders (including customers with none), all products and their sales (including unsold ones). That 'including the ones with nothing' is exactly what an INNER JOIN throws away, so choosing the wrong join silently hides rows.

Where used: customer lists that must include zero-order customers, inventory reports including unsold products, left-outer lookups in API endpoints

Why learn this

Building list endpoints that must include rows with no related records (e.g. users with zero orders)
Spotting why a query 'lost' rows — an INNER JOIN dropping unmatched rows is one of the most common SQL bugs

Query walkthrough

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Focus: Watch Greg and Hana survive the join with order_id = NULL — the rows an INNER JOIN would delete.

Common mistakes

Filtering the right table in WHERE turns LEFT into INNER: Adding WHERE o.status = 'paid' to a LEFT JOIN drops the NULL rows (NULL = 'paid' is not true), silently making it an inner join. Put the condition in the ON clause (ON o.customer_id = c.id AND o.status = 'paid') to keep the unmatched left rows.
Using RIGHT JOIN where a LEFT JOIN reads better: RIGHT JOIN keeps the right table's rows, but most people read top-to-bottom. Swap the tables and use LEFT JOIN — same result, clearer intent.
Counting the wrong column to get zero: count(*) counts NULL-padded rows too, so customers with no orders show 1 instead of 0. Count a right-table column — count(o.id) — which skips NULLs and correctly yields 0.

Glossary

preserve: To keep something exactly as it is and prevent it from being lost or removed, like keeping unmatched rows.
spine: The main structure or foundation of a query, usually the starting table to which other data is attached.
trim: Extra or optional information added to the main structure of a query, like details attached from a secondary table.

Recall questions

What does a LEFT JOIN keep that an INNER JOIN does not?
What value fills the right-hand columns when a left row has no match?
How can you rewrite a RIGHT JOIN as a LEFT JOIN?

Understanding checks

Which customers does this return, and why?

Greg, Hana

The LEFT JOIN gives Greg and Hana a NULL order row because they placed no orders. WHERE o.id IS NULL then keeps only those unmatched left rows — the standard 'anti-join' pattern for finding rows with no related record.

What single number does this return?

There are 8 order rows that match a customer, plus 2 customers (Greg, Hana) kept as NULL-padded rows = 10. An INNER JOIN would return only the 8 matched rows.

Practice tasks

Don't lose the customers without orders

This query uses INNER JOIN, so customers who never ordered disappear. Change it to a LEFT JOIN so EVERY customer is listed, with order_id NULL when they have no orders. Keep the ordering.

Challenge

All US customers and their order dates

List every US customer with each of their order dates, INCLUDING US customers who have no orders (NULL date). Use a LEFT JOIN, filter on the customer's country (not the orders), and order by name then date.

Continue learning

Previous: SELECT, FROM & Columns

Previous: WHERE & Operators

Previous: INNER JOIN

Next: FULL OUTER & CROSS JOIN

Next: Self Join

Return to SQL Roadmap