Keeping All Rows with LEFT JOIN

JOIN leaves out the rows without a match: in a list of customers with their orders, there's no Henry Scott and no Max Baker. Often, though, you want all the rows of one table, whether they have a match or not. That's what LEFT JOIN is for:

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

The table before LEFT JOIN, here customers, is the left one, and the table after it is the right one. LEFT JOIN keeps every row of the left table. The rows that have a match are paired just as with JOIN. A row without a match stays in the result once, and the columns of the right table are NULL in it: Henry Scott has no order, so the ID and the date of his order are NULL. And because NULL comes first when you sort, the customers without an order are at the top.

So now it matters which table comes first. FROM orders AS o LEFT JOIN customers AS c … keeps every order, and since every order has a customer, it finds nothing more than JOIN. Put first the table whose rows you want to keep. You may also see LEFT OUTER JOIN, which is the same thing.

There's also RIGHT JOIN, which keeps every row of the right table, and FULL JOIN, which keeps the rows of both. SQLite has both, but you'll rarely need them: a RIGHT JOIN is a LEFT JOIN with the tables swapped, and most programmers write LEFT JOIN.

Careful when another table joins after a LEFT JOIN. Say you also want the titles of the games in each customer's orders, so you add JOIN order_items AS i ON i.order_id = o.id. For Henry Scott, o.id is NULL, and NULL equals nothing, so his row finds no item, and the plain JOIN throws it out again. A table that joins onto the right table of a LEFT JOIN usually needs a LEFT JOIN too.