In the previous lesson you lost three customers, three products and ten orders without anybody warning you. The LEFT JOIN is the tool that gets them back. Its rule is a single sentence: it keeps every row of the left table, whether or not it matches the right one; when they don't match, the right-hand columns are filled with NULL.

It sounds simple, and it is. But the LEFT JOIN hides the most famous trap in all of intermediate SQL: putting a condition in the ON or in the WHERE stops being a matter of indifference. The difference raises no error, gives no warning, and produces two different results that look equally plausible. Half of this lesson is devoted to making sure you never fall for it.

1. The LEFT JOIN rule and the set diagram
2. LEFT JOIN = LEFT OUTER JOIN
3. Where exactly the NULLs come from
4. GreenStore's three real cases
5. The anti-join pattern: finding what doesn't match
6. The trap: a condition in ON versus a condition in WHERE
7. Chained LEFT JOINs
8. When the right table has several rows per left-hand one
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

As a flow of rows, extending the diagram from 03-02:

That extra step —"added anyway with NULL on the right"— is step 1d of the logical order you saw in 03-01. It happens inside the FROM, after applying the ON and before the WHERE. Everything this lesson explains follows from that fact.

The word "left" is literal: it's the table you write before the words LEFT JOIN.

From this comes the most important consequence for writing well: the LEFT JOIN isn't commutative. customers LEFT JOIN orders keeps all 15 customers; orders LEFT JOIN customers keeps all 20 orders. They're different questions.

How to choose the left side: say the business question out loud and look for the noun that must appear complete in the report. "I want every customer, with their orders if they have any" → customers is the left. "I want every order, with its sales rep if there is one" → orders is the left.

The two spellings are identical:

OUTER is optional and practically nobody writes it. The adjective outer describes the whole family: LEFT, RIGHT and FULL are outer joins because they keep rows that fall outside the matching; INNER is the inner join because it only returns what stays inside.

In this course we write LEFT JOIN.

This point is often misread, so it's worth being precise: the NULLs that appear in a LEFT JOIN weren't in the database. They're manufactured by the engine at the moment the result is built.

When a left-hand row finds no partner, PostgreSQL adds it to the result anyway and fills all of the right table's columns with NULL. Not just the key one: all of them.

Two consequences you'll use constantly follow from that:

1. You can detect the absence by looking at any column on the right. If o.id IS NULL in a LEFT JOIN from customers, it means there was no partner. It's the basis of section 5's anti-join.
2. You should look at a NOT NULL column on the right. If you picked a column that can genuinely be null in the data, you wouldn't be able to tell "there was no partner" from "there was a partner and that value was empty". That's why the anti-join is always written against the right table's primary key: o.id, ol.id, r.id. A PK is never legitimately NULL.

A third, subtler effect is the null arithmetic you saw in 02-02: any operation involving NULL gives NULL. If you compute o.shipping_cost * 2 over a partnerless row, the result is NULL, not zero. The COALESCE function that solves this is studied in 06-04; the complete treatment of nulls, in 04-03.

The dataset's three deliberate gaps exist precisely for this lesson.

23 rows. Compare with 03-02:

There they are: Núria, Hugo and Inés, with the whole orders part at NULL. The arithmetic is exact and worth internalising: the result of a LEFT JOIN has as many rows as the INNER JOIN plus one row per orphan left-hand row.

Same pattern, now over the catalogue. We trim it down to five products so the contrast is clear:

Products 12 and 18 have been sold three times each and take up three rows. Products 13, 19 and 20 have never been sold and take up one row with everything at NULL.

Without the trimming WHERE, the complete query returns 50 rows: the 47 from order_lines plus the 3 from the never-sold products.

The third case is different from the previous two, and the nuance is worth noticing. Here it isn't the child row that's missing: the foreign key is NULL.

20 rows: all 20 orders. Against the 10 the INNER JOIN of 03-02 returned. The NULLs in the sales_rep column mean something perfectly legible for the business here: order placed through the web, with no sales rep assigned. Ten out of twenty, exactly the proportion 01-06 described.

Notice one detail: sales_rep is NULL on two counts. First because o.employee_id is NULL and matches nothing; and second because, even if it did match, the concatenation e.name || ' ' || e.last_name over null columns gives NULL (the trap from 02-02).

So far we've used the LEFT JOIN to keep what doesn't match. Now we're going to use it to keep only that, which is one of the most requested queries in any company: inactive customers, products with no turnover, unpaid invoices, unverified users.

The technique is called an anti-join and it's written in two moves:

1. A LEFT JOIN that keeps everything from the left.
2. A WHERE <right table's primary key> IS NULL that keeps only the rows that found no partner.

Three rows. This is exactly the list the marketing department would ask for to launch a first-purchase campaign.

Three rows, and with a different diagnosis for each: the candles don't sell because there's no stock; the spirulina because it's been discontinued (active = false); the deodorant has stock and is active, so its problem is commercial. A well-framed JOIN doesn't just return data: it points at the cause.

About IS NULL: it's the correct operator for asking about nulls, because = NULL is never true (you saw it in 02-03 with employee_id = NULL returning 0 rows). Its complete treatment, together with IS NOT NULL and COALESCE, is lesson 04-03.

Note: in module 7 you'll see two other ways of writing the same thing: NOT EXISTS with a correlated subquery, and NOT IN. All three have the same goal and different behaviour around nulls. The anti-join with LEFT JOIN is the one you can write today, and it's perfectly idiomatic.

Here's the most important content of the lesson. Pay attention to the question, because the key is in it:

"Give me every customer, with their delivered orders."

Notice the "every": the report must list all 15 customers, whether or not they have delivered orders. Let's write it both possible ways.

18 rows and all 15 customers present. The ones with no delivered order appear with NULL: Núria, Hugo and Inés because they've never bought, and Ana Belmonte Roca because her two orders are cancelled and paid, neither of them delivered. Ana is the most interesting case: she exists in orders, but none of her orders passes the ON condition.

We change exactly two words: AND becomes WHERE.

14 rows and only 12 customers. Ana, Núria, Hugo and Inés have disappeared. The LEFT JOIN has behaved like an INNER JOIN.

Go back to the logical order from 03-01 and follow Núria's row through each version:

Step 1d does add Núria in both versions. The difference comes afterwards: in version B, the WHERE evaluates o.status = 'delivered' over a row whose o.status is NULL. And NULL = 'delivered' isn't FALSE, it's NULL, which isn't TRUE either, so the row is discarded. It's the same mechanism from 02-03 acting in a new place.

The rule to burn into your memory: in a LEFT JOIN, any condition in the WHERE over a column of the right table turns the LEFT into an INNER, because the rows padded with NULL can't satisfy it.

All three are valid: each answers a different question. The mistake isn't using the WHERE, it's using it believing it does what the ON does.

One case that is safe: a condition in the WHERE over a column of the left table degrades nothing. WHERE c.country = 'Spain' filters customers, and the ones that pass the filter keep their NULLs on the right without any trouble. The danger is exclusively with right-hand columns.

With three or more tables the LEFT JOIN keeps its logic, but a surprising rule shows up: an INNER JOIN placed after a LEFT JOIN cancels the LEFT's effect.

We want "every customer, with their orders and the sales rep of each order". Written with LEFT on both hops:

And now the same path with an INNER JOIN on the second hop:

The reasoning: JOINs are resolved left to right, so (customers LEFT JOIN orders) produces 23 rows; over those 23, the INNER JOIN with employees demands that o.employee_id match an employee. The 3 rows of customers with no orders have o.employee_id = NULL and fall away; the 10 rows of web orders have employee_id = NULL and fall away too. Ten remain.

Practical rule: once you've opened a chain with a LEFT JOIN, every subsequent hop on that branch must be a LEFT JOIN. A single INNER in the middle undoes all the work, and it does so without any warning.

It's a very easy mistake to make when extending an existing query: somebody adds JOIN products ON ... at the end of a query that started with a LEFT JOIN, and the report loses rows overnight without anybody touching the LEFT.

The LEFT JOIN doesn't protect you from 03-02's row multiplication. There's still one row per pairing:

Four products produce seven rows: two reviews for products 1, 2 and 6, and one empty row for number 4. Over the whole catalogue, products LEFT JOIN reviews returns 23 rows: the 12 reviews plus the 11 products with none.

The general arithmetic of a LEFT JOIN is always this:

And once again module 4's warning: if over the first case you summed c.signup_date, or counted customers, you'd get inflated figures, because Lucía appears three times. A LEFT JOIN keeps rows; it doesn't deduplicate them.

• Putting a condition on the right table in the WHERE. It degrades the LEFT JOIN to an INNER JOIN without saying a word. It's the mistake of this lesson: 18 rows against 14, and four customers gone.
• Slipping an INNER JOIN in after a LEFT JOIN. The same effect, in a chain: 23 rows become 10.
• Writing the tables the wrong way round. orders LEFT JOIN customers isn't customers LEFT JOIN orders. The LEFT JOIN isn't commutative; the left is the one kept in full.
• Doing the anti-join against a nullable column. WHERE o.employee_id IS NULL doesn't tell "there was no partner" from "there was a partner with an empty value". Always compare against the right side's primary key.
• Trying an anti-join with an INNER JOIN. INNER JOIN ... WHERE right.id IS NULL always returns zero rows: the INNER has already thrown those rows away.
• Using = NULL instead of IS NULL. WHERE o.id = NULL always returns zero rows (02-03).
• Assuming NULL in the result means zero. o.shipping_cost is NULL, not 0.00, for a customer with no orders. Operating on it propagates the null (COALESCE, in 06-04).
• Forgetting that the LEFT JOIN still multiplies rows. It keeps the orphans, but it doesn't stop a customer with three orders taking up three rows.
• Tip: write the INNER JOIN first, check the count, and then change it to LEFT. The difference between the two counts tells you exactly how many orphans there are.
• Tip: for the anti-join, say the question out loud. "Customers with no orders", "products never sold", "invoices not paid". The "no" and the "never" are the signal that it's time for LEFT JOIN ... WHERE ... IS NULL.
• Tip: be suspicious of any WHERE that names the right table of a LEFT JOIN. Unless it's a deliberate IS NULL, it should almost always be in the ON.

The product team wants to know which catalogue references have received no review at all, so they can launch a campaign asking for opinions. Write the query that returns the id, name, category and price of those products, sorted by id.

Then answer: why can the JOIN with categories be an INNER JOIN without breaking the anti-join?

Logistics needs the complete listing of the 20 orders with their return information, if there was one: order id, date, status, shipping cost and —when a return exists— its reason and amount.

1. Write the query.
2. Then write the variant that returns only the orders that had no return, and state how many rows it gives.

A colleague hands you this query and says: "I want every customer with their orders paid by card, but I'm missing customers".

1. Explain what that query is really doing.
2. Fix it so that it returns what he wants.
3. Predict how many rows each version returns and how many distinct customers appear in each.

Solution 1

11 rows, exactly the 11 products with no review that 01-06 announced. Only 9 of the 20 products have ever been reviewed.

Why the JOIN with categories can be an INNER: because section 7 warns about INNER JOINs that appear after a LEFT JOIN on the same branch. That isn't the case here: categories hangs off products through an FK pointing at a category that always exists, so that INNER JOIN discards no product. The 20 starting rows are still 20 before the LEFT JOIN with reviews is applied. The precise rule is: an INNER JOIN is safe as long as it can't remove rows from the protagonist table.

(If category_id allowed NULL with genuinely null data, that INNER JOIN would indeed lose products and it would have to be written as a LEFT JOIN too.)

Solution 2

1. Every order with its return, if there was one:

It returns 20 rows: the 3 with a return (orders 6, 10 and 13) show a reason and an amount; the other 17 show *(null)* in those two columns.

2. Only the orders with no return (anti-join):

17 rows = 20 orders − 3 returns. Exactly orders 6, 10 and 13 are missing.

Solution 3

1. What it's really doing. The condition o.payment_method = 'card' is in the WHERE and applies to a column of the right table. Every customer row with no order reaches the WHERE with o.payment_method at NULL, and NULL = 'card' isn't TRUE. Result: the LEFT JOIN degrades to an INNER JOIN and the query returns only the customers who have at least one order paid by card. It's a legitimate question, but not the one he wanted.

2. The fix: move the condition into the ON.

3. Row prediction. The orders with payment_method = 'card' are ids 1, 3, 5, 6, 8, 10, 11, 13, 15, 16 and 19: 11 orders, from 9 distinct customers (Lucía appears three times).

The 6 customers who appear with NULL in the fixed version are: Tiago (8), Julien (10), Diego (12), Núria (13), Hugo (14) and Inés (15). The last three because they've never bought; the first three because they did buy, but paying by PayPal or transfer.

The LEFT JOIN is, in practice, the JOIN that solves the most business problems:

• It keeps every row of the left table, matching or not, padding the right-hand columns with NULL. LEFT JOIN and LEFT OUTER JOIN are the same thing.
• The NULLs are manufactured by the engine as it builds the result; they weren't in the data. That's why you can detect the absence by looking at the right table's primary key.
• You've recovered GreenStore's three gaps: customers 13, 14 and 15 (23 rows instead of 20), products 13, 19 and 20 (50 rows instead of 47) and the 10 web orders (20 rows instead of 10).
• The anti-join pattern LEFT JOIN ... WHERE right.id IS NULL answers the questions with "no" and "never" in them: three customers who haven't bought, three products nobody has sold, eleven products with no reviews.
• You know that a condition on the right table in the WHERE degrades the LEFT JOIN to an INNER JOIN: 18 rows and 15 customers against 14 rows and 12 customers, with the same question written two ways. If you want "every X with their Y meeting Z", the condition goes in the ON.
• An INNER JOIN chained after a LEFT JOIN cancels its effect: 23 rows drop to 10. Once you've opened a branch with LEFT, carry on with LEFT.
• The LEFT JOIN doesn't deduplicate: it still multiplies rows when the right side has several per left-hand one.

In the next lesson, RIGHT JOIN, we'll see the exact mirror image of what you've just learned. You'll check that A RIGHT JOIN B and B LEFT JOIN A return exactly the same thing, you'll understand why most style guides prefer the LEFT in spite of that, and you'll finally meet Irene Salvador Mira and Daniel Vercher Lluch, the two employees who have never handled an order.