A scalar subquery that returns more than one row is a runtime error data volume decides — works in dev, dies in prod.

A scalar subquery must return at most one row — the engine errors the moment it returns two. It works while the lookup key is unique and breaks silently later, so the cardinality is a fragility, not a correctness win.

SELECT
  id,
  (
    SELECT
      cents FROM
  prices
    WHERE
      prices.sku = items.sku
  ) AS price
FROM
  items
ORDER BY
  id;

SELECT
  i.id,
  p.cents AS price
FROM
  items i
  JOIN prices p ON p.sku = i.sku
ORDER BY
  i.id;

It reads correctly today — but the day a SKU gets a second price row, the scalar subquery errors in production; a join on the unique key stays safe.

Join on the key when it's unique, or wrap the subquery in an aggregate (MAX, ANY_VALUE) that is defined for many rows, so the query survives a duplicate.

(SELECT MAX(price) FROM prices WHERE prices.sku = p.sku)