Comment by procaryote
17 hours ago
I've generally started to treat use of SELECT DISTINCT as a warning flag, as it's very common that it indicates bad code
Some use it because they don't understand uniqueness constraints and try to fix it in post so to say. Some use it because they forgot a join condition and are absolute amateurs. Some use it because it fixed a problem for them once and now they add it everywhere
These people seem to outnumber the people who use SELECT DISTINCT in a well thought out manner
This. 100%. So on point.
I'd only add one more observation.
Sometimes the root cause is poor table design (or in analytic/OLAP use cases poor ETL design without proper data validation checks or handling) where uniqueness is not enforced and that is the root cause that should be fixed if at all possible. A "first normal form" violation in the database design so to speak.
If that root cause is not addressed, then SELECT DISTINCT is more often necessary and the SELECT DISTINCT disease to be safe culture and behavior in the code base on top of the database just spreads.
Yeah, it's almost always more intention-revealing to use CTEs and WHERE EXISTS.
Yeah, people have been advising against DISTINCT for ages. I guess that piece of common wisdom somehow got lost in this age of AI wonders. :D
It's definitely a warning flag if you're applying it to full-ish rows. This situation seems much more innocuous to me.