English is currently available.
SQL DISCUSSION
I have a customers table with 100 rows and an orders table. I want every customer listed with the number of orders placed in 2026, including customers with none. SELECT c.name, COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.order_date >= '2026-01-01' GROUP BY c.name returns only 63 rows.
I thought LEFT JOIN keeps all rows from the left table. Why are the other 37 customers missing, and what exactly is the difference between the join types?
The join types differ only in what happens to rows without a partner. INNER JOIN returns only the pairs where the ON condition is true. LEFT JOIN returns those pairs plus every left-table row that had no match, with NULL in all columns of the right table. RIGHT JOIN is the mirror image, and FULL JOIN keeps the unmatched rows of both sides. The word OUTER is optional: LEFT JOIN and LEFT OUTER JOIN are the same thing.
So after your join, a customer with no orders is present as one row whose o.order_date is NULL.
The WHERE clause runs after the join. For the customers without orders, o.order_date >= '2026-01-01' compares NULL with a date, which is not true, so those rows are removed again. The same happens to customers whose orders are all from earlier years. The filter has turned the outer join back into an inner join.
Move the condition on the right-hand table into the ON clause: LEFT JOIN orders o ON o.customer_id = c.id AND o.order_date >= '2026-01-01'. Now the date only decides which orders are allowed to match, and every customer survives.
After that fix there is a second bug: COUNT(*) counts rows, and a customer with no matching order still has one row (the NULL-extended one), so they would show 1 instead of 0. Count a column from the right table instead: COUNT(o.id) ignores NULLs and gives 0 for those customers.
It is also safer to group by the key, GROUP BY c.id, c.name, because two different customers can share a name and would otherwise be merged into one line.
A rule that helps: conditions on the preserved (left) table go in WHERE, conditions on the optional (right) table go in ON. The one deliberate exception is the anti-join, where you want only the unmatched rows: LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL lists customers that have never ordered.
For an INNER JOIN it makes no difference to the result whether a filter is in ON or WHERE, which is why the habit of putting everything in WHERE works until the first outer join.
Watch for row multiplication when you add a second one-to-many join to the same query. If a customer has 3 orders and 2 support tickets and you join both tables to customers, that customer produces 3 × 2 = 6 rows, and COUNT or SUM over either table is inflated. Aggregate each child table in its own subquery first and join the one-row-per-customer results, or use COUNT(DISTINCT o.id) when only counts are needed.
