LEFT JOIN: Keeping the Unmatched

From lesson 5-2, an INNER JOIN on customers.id = orders.customer_id drops an order whose customer_id matches no customer row. It vanishes from the result entirely.

An inner join keeps only the rows that find a partner. No match means no output row, in either direction. This lesson introduces the join that keeps the lonely rows instead.

The customer who never ordered

Meet Dan from Oslo, customer id 4, zero orders. Ask "show each customer with their orders" using the INNER JOIN from unit 5 and Dan simply vanishes: he has no matching order row, so he produces no output.

Often that is wrong. "All customers and their orders" should include the quiet ones. That is LEFT JOIN:

SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

LEFT JOIN keeps every row of the left table (the one named before the join, here customers). When a row finds matches it joins normally. When it finds none, it still emits one row, with every column from the right table set to NULL. In the output style used here, that NULL is the empty spot after the last |.

The same query with JOIN and with LEFT JOIN

The customers table now has a fourth row, Dan from Oslo, who has never ordered anything. The same query runs twice, as an inner join before the divider and as a left join after it.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');
INSERT INTO customers VALUES (4, 'Dan', 'Oslo');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

SELECT customers.name, orders.item
FROM customers
JOIN orders ON orders.customer_id = customers.id
ORDER BY customers.id, orders.id;
SELECT '---';
SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
ORDER BY customers.id, orders.id;

Output

Ana|coffee
Ana|bagel
Ben|sandwich
Ben|coffee
Cara|coffee
---
Ana|coffee
Ana|bagel
Ben|sandwich
Ben|coffee
Cara|coffee
Dan|

The only difference is the extra Dan| line, and the empty space after the pipe is a NULL. A LEFT JOIN keeps every row from the left table, the one named in FROM, and fills the right-hand columns with NULL when no partner exists.

Which table is "left" therefore decides the whole meaning of the query. FROM customers LEFT JOIN orders guarantees every customer appears, while swapping the two would instead guarantee every order appears.

The find-the-missing pattern

LEFT JOIN combined with IS NULL from lesson 2-3 isolates the rows that have no partner:

SELECT customers.name
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
WHERE orders.id IS NULL;

Every matched customer has a real orders.id in their joined row, and only the unmatched ones carry NULL there. Filtering on it leaves exactly the customers who never ordered.

This shape is worth memorizing, because it comes up constantly: users who never logged in, products never sold, students enrolled in no course, invoices with no payment.

One detail makes it reliable. The column tested must be one that can never be NULL in a real matched row, which is why a primary key like orders.id is the usual choice.

Listing the customers who never ordered

Adding the IS NULL test to the left join isolates exactly the unmatched rows.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');
INSERT INTO customers VALUES (4, 'Dan', 'Oslo');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

SELECT customers.name
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
WHERE orders.id IS NULL
ORDER BY customers.name;

Output

Dan

Without the WHERE, the query returns every customer once per order plus Dan. Unmatched rows carry NULL in every column that came from orders, so orders.id IS NULL keeps only them, and Ana, Ben, and Cara all drop out.

As lesson 2-3 established, this has to be IS NULL and never = NULL. Writing WHERE orders.id = NULL returns an empty result with no error at all, which looks exactly like "every customer has ordered" and is the usual way this pattern gets broken.

It also matters that the test names a column which can never legitimately be NULL, such as a primary key. Testing a nullable column like orders.price would also match real orders whose price was simply never recorded.