What is a left outer join?
A left outer join is a join that keeps every row from the first (left-hand) table, whether or not it matched anything in the second (right-hand) table. Where there was no match, the right-hand columns come back as NULL.
A worked example
You have 100 signed-up customers, 30 of whom have never placed an order. You want a list of every customer with their order count:
SELECT
customers.name,
COUNT(orders.id) AS order_count
FROM customers
LEFT OUTER JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name
Before: 100 customers, 30 with no matching order rows.
After: 100 rows in the result. The 30 order-less customers appear with order_count = 0. Run the same query as an inner join and you’d get 70 rows — the customers you most want to know about would have vanished.
One detail worth internalizing: COUNT(orders.id) returns 0 for those rows, but COUNT(*) returns 1, because the unmatched customer still occupies one output row. Count a column from the right-hand table, not the whole row.
Finding the rows that didn’t match
Because unmatched rows survive with NULLs, a left outer join is also how you find absences. Filter for a NULL on the right-hand side and you get exactly the rows with no counterpart:
SELECT customers.name
FROM customers
LEFT OUTER JOIN orders ON orders.customer_id = customers.id
WHERE orders.id IS NULL
That pattern — sometimes called an anti-join — answers questions like “which accounts have no activity this quarter?” or “which products were never sold?” It’s the everyday tool behind churn and engagement analysis.
Put the condition in the WHERE clause deliberately, though. Any other filter on a right-hand column (WHERE orders.total > 50) will discard the NULL rows and turn your left join back into an inner join by accident. If you want to restrict which rows are eligible to match, put that condition in the ON clause instead.
Left vs. right
A LEFT JOIN B and B RIGHT JOIN A return the same rows. Left joins are far more common simply because reading order matches evaluation order: you start with the table you care about and hang optional information off it. See right outer join for when the mirror image reads better.
Left outer joins in Metabase
The query builder uses a left outer join by default when you add a join step, which matches the usual intent: keep everything from the table you started with, and enrich it. You can switch the join type from the picker in the notebook editor. In a native query you have to spell it out, since a bare JOIN in SQL means an inner join.
Key article
Related terms
Further reading
Put it to work
- Churn rate — Metric
- Customer health dashboard — Dashboard
- Customer success analytics — Overview
