A scalar subquery in SELECT behaves like a LEFT join (NULL on no match); an INNER JOIN drops the row — 'same query, rewritten for speed' silently loses entities.

The two forms are only equivalent when every outer row has a match. The subquery evaluates per outer row and returns nothing → NULL when the inner set is empty; the inner join makes matching a requirement for existing. 'Rewrote for performance, entities vanished' is the classic incident — and the fix keeps the performance intent: LEFT JOIN + GROUP BY, with the NULL-versus-COALESCE choice stated.

SELECT
  b.blend,
  MAX(ba.batch_no) AS latest
FROM
  blends b
  JOIN batches ba ON ba.blend = b.blend
GROUP BY
  b.blend
ORDER BY
  b.blend;

SELECT
  b.blend,
  MAX(ba.batch_no) AS latest
FROM
  blends b
  LEFT JOIN batches ba ON ba.blend = b.blend
GROUP BY
  b.blend
ORDER BY
  b.blend;

The rewrite lost the new blend with no batches — a scalar subquery in SELECT is left-join-shaped and reads NULL, but the INNER JOIN rewrite drops the entity.

LEFT JOIN plus an aggregate, or keep the subquery where N is small and clarity wins.