distinct has always felt like a query smell to me. Too many junior analysts abusing it because they don't know the schema well and are over-joining entities
Sometimes the number of joins is fine but they don’t understand the data properly and should be spending more time understanding why multiple rows are being returned when they expect one (eg they need to filter on an additional field).
I wish SQL had a strict mode syntax that forces you to use something like `select one` (like LINQ’s Single()) or `select many` to catch these kinds of bugs.
DISTINCT is often a smell at the head (or middle) of a complex query as you are throwing away processed information, sometimes a lot of it, late in the game. Much better to filter it out earlier and not process it further, where possible. Filtering earlier, as well as reducing waste processing time (and probably memory use), increases the chance of the query planner being able to use an index for the filter which could greatly decrease the IO cost of your query.
Comments
distinct has always felt like a query smell to me. Too many junior analysts abusing it because they don't know the schema well and are over-joining entities
Sometimes the number of joins is fine but they don’t understand the data properly and should be spending more time understanding why multiple rows are being returned when they expect one (eg they need to filter on an additional field).
I wish SQL had a strict mode syntax that forces you to use something like `select one` (like LINQ’s Single()) or `select many` to catch these kinds of bugs.
DISTINCT is often a smell at the head (or middle) of a complex query as you are throwing away processed information, sometimes a lot of it, late in the game. Much better to filter it out earlier and not process it further, where possible. Filtering earlier, as well as reducing waste processing time (and probably memory use), increases the chance of the query planner being able to use an index for the filter which could greatly decrease the IO cost of your query.