SQL Design Patterns (2010)

Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.

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

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.

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.

Huh, I have always just thought of it as a syntactic shortcut.

SELECT DISTINCT is often a code smell. (Not always.) If you see it, there’s a 70% chance it got slapped on to fix an issue that should have been solved a different way.

SELECT DISTINCT ON is different, and useful.

I had a teacher who had specific rules for exams when we wrote SQL statements:

- For a question worth 2 points, if you use the word "DISTINCT" when it wasn't needed, you lose 0.5 points.

- If you don't use "DISTINCT" when it was necessary, you lose all 2 points.

Oh yes, introducing a little game theory anxiety into exam questions sounds like a wonderful little torturing tool!