A WHERE filter on the right table discards the NULL-extended rows — your LEFT JOIN silently becomes INNER; right-side filters belong in ON.

A LEFT JOIN keeps unmatched left rows with NULL-extended right columns. A WHERE that tests a right-side column is UNKNOWN on those NULL rows, so WHERE drops them and the outer join collapses to inner.

SELECT
  c.name,
  COUNT(o.customer_id) AS n
FROM
  customers c
  LEFT JOIN orders o ON o.customer_id = c.id
WHERE
  o.order_date >= DATE '2024-01-01'
GROUP BY
  c.name
ORDER BY
  c.name;

SELECT
  c.name,
  COUNT(o.customer_id) AS n
FROM
  customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  AND o.order_date >= DATE '2024-01-01'
GROUP BY
  c.name
ORDER BY
  c.name;

The 'all customers incl. zero orders' report quietly drops every zero-order customer — the rows it exists to show.

Move the predicate into the ON clause, or test for the null with WHERE col = value OR col IS NULL.

LEFT JOIN t ON t.fk = base.id AND <right-side predicate>