Browse Teach Curate

A DISTINCT That Hides a Row-Multiplying Join

When someone joins to a child table just to check that a related row exists, each parent row comes back once per child row, and a DISTINCT is added to hide the duplicates. The engine still produces every duplicate and then sorts or hashes them away, which is where the time goes. Replacing the join with EXISTS asks the real question and keeps the row count right from the start.

Questions this Concept answers

Why does adding `DISTINCT` to hide join duplicates cost so much?

jeremy Text

Optimising DISTINCT Clauses using EXISTS

Joining a parent table to a child table returns the parent row once for every child row that matches, so a customer with five matching orders comes back five times. Adding DISTINCT hides that, but th…