LEFT JOIN: keeping the left table

The problem with INNER JOIN

The INNER JOIN discards rows without a match. If a category has no products, it does not appear. If a product has no assigned category (category_id is NULL), it does not either. Sometimes that is exactly what you do not want.

LEFT JOIN

The LEFT JOIN (or LEFT OUTER JOIN) keeps all the rows from the left table (the one in the FROM), whether or not they have a match on the right. When there is no match, the columns of the right table are filled with NULL.

SELECT categories.name, products.name
FROM categories
LEFT JOIN products ON products.category_id = categories.id;

Here the left is categories. The result includes all the categories; those with no products still appear, with NULL in the product column:

When to use it

LEFT JOIN is the tool for answering questions like "which rows on the left have NO match?". We filter by the resulting NULL:

-- Categories WITH no products.
SELECT categories.name
FROM categories
LEFT JOIN products ON products.category_id = categories.id
WHERE products.id IS NULL;

To check whether something is null you use IS NULL / IS NOT NULL, never = NULL (in SQL, NULL = NULL is not true).

The side matters: A LEFT JOIN B keeps all of A. If you wanted to keep all of B, you either swap the order, or use RIGHT JOIN (which is the symmetric one).

Examples

LEFT JOIN: all categories, with products or not

SELECT categories.name AS category, products.name AS product
FROM categories
LEFT JOIN products ON products.category_id = categories.id
ORDER BY categories.name, products.name;