15.2 LEFT JOIN: All Rows from the Left, NULLs Where No Match

Right, let’s talk about the LEFT JOIN. This is the workhorse of the relational world, the one you’ll use more often than any other. It’s the Swiss Army knife for when you want to ask, “Show me everything from this table, and if you happen to have any matching info from that table, tack it on. If you don’t, just give me NULLs and I’ll deal with it.”

The mental model is simple, but the implications are everything. The official definition is: a LEFT JOIN returns all records from the left table (the one you mention first, before the JOIN keyword), and the matched records from the right table (the one after JOIN). The result is NULL from the right side if there is no match.

The Core Concept: A Loyal Left Table

Think of it this way: the left table is your priority. It’s the guest of honor. The right table is just the plus-one. The left table gets to show up to the party no matter what. The right table only gets in if its name is on the list (i.e., it has a matching key). If it’s not, we don’t kick everyone out; we just leave an empty seat for it, marked NULL.

Let’s get concrete. Imagine we have a table of customers and a table of orders. Not all customers have placed orders yet (maybe they just signed up). If we want a list of every single customer and any orders they might have, this is our tool.

-- Setting the stage
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    amount DECIMAL(10, 2)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES (101, 1, 50.00), (102, 1, 25.00), (103, 3, 100.00);
-- Note: Bob (id 2) has no orders. Poor Bob.

Now, let’s get that complete customer list.

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

Your result will look something like this:

Look at Bob. There’s our guy. The LEFT JOIN ensured he wasn’t left out (pun intended, this time). His row is preserved, and the columns from the orders table are gracefully filled with NULL. This is the quintessential use case.

The Subtle Art of the WHERE Clause

Here’s where most people face-plant. You must understand the order of operations. The ON clause is part of the JOIN operation itself—it defines the link between the tables. The WHERE clause filters the result set after the join has already happened.

This distinction is everything. Let’s say you want to find only customers who have never placed an order. You might be tempted to do this:

-- WRONG (or at least, not what we intend here)
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 0; -- This filters out the NULLs!

This fails because NULL > 0 is not TRUE; it’s unknown. So Bob’s row gets eliminated. To find the Bobs of the world, you need to explicitly look for the NULL that the join created.

-- CORRECT: Find customers with no orders
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This works perfectly because the WHERE clause filters for the rows where the join failed to find a match, which we know because the primary key (order_id) from the right table is NULL. This pattern is your best friend for finding orphaned records.

Questionable Choices and the RIGHT JOIN

You will occasionally see its mirror image, the RIGHT JOIN or RIGHT OUTER JOIN. It does the exact same thing as a LEFT JOIN, but prioritizes the right table. All rows from the right, NULLs from the left where no match exists.

My strong, unequivocal opinion is that you should almost never use it. Why? Readability. SQL is read from top to bottom and left to right. A query with a RIGHT JOIN forces you to mentally re-orient the tables. It breaks the flow. If you need a right join, just rewrite your query by swapping the table order and using a left join. It’s logically identical and much easier for the next person (who might be future-you) to parse. The existence of RIGHT JOIN feels like a committee-based design choice made for theoretical completeness rather than practical utility.

The UNION Gotcha

A common “clever” trick is to simulate a FULL OUTER JOIN (which some databases, like MySQL, don’t support natively) by doing a LEFT JOIN and a RIGHT JOIN and UNIONing them together. If you do this, you must be incredibly careful. A UNION removes duplicate rows. If your LEFT JOIN and RIGHT JOIN produce a perfectly mirrored row for a matched record (which they often will), the UNION will deduplicate it, and you’ll only get one instance of it. You usually want UNION ALL in this scenario to preserve all rows from both result sets, duplicates and all. It’s a messy solution, but sometimes it’s the only one you’ve got.