Why my LEFT JOIN duplicated rows

I needed to filter machines by the GPU cards they have. Machines are in one table, GPUs in another, one row per card.

SELECT node.* FROM node
JOIN node_gpu ON node_gpu.node_twin_id = node.twin_id
WHERE node_gpu.device = '...';

I first wrote this as a LEFT JOIN, but a WHERE on the right table throws away the unmatched rows anyway, so it behaves like an inner join. Plain JOIN says what it does.

It worked until a machine had two cards. Then it came back twice, because a one-to-many join multiplies the “one” side.

DISTINCT on node.* looks fine since I never return GPU columns. It fails if node has a json column (not jsonb), because json has no equality operator.

So I tried DISTINCT ON (node.twin_id) and got this:

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

The response is ordered so the caller’s rented machines come first:

ORDER BY (rented_by = $1) DESC NULLS LAST, node.node_id

DISTINCT ON wants its column to lead the ORDER BY, and that order had to stay first.

A subquery with plain SELECT DISTINCT node_twin_id works, since the duplicates never reach the outer query. EXISTS is cleaner:

SELECT node.* FROM node
WHERE EXISTS (
  SELECT 1 FROM node_gpu
  WHERE node_gpu.node_twin_id = node.twin_id
    AND node_gpu.device = '...'
)
ORDER BY (rented_by = $1) DESC NULLS LAST, node.node_id;

It’s a semi-join, so duplicates can’t happen and the ORDER BY stays free.

TIL: when a join only filters, use EXISTS.