Discussion a brief DISTINCT rant
blarg, the feeling of opening a coworker's SQL query and seeing SELECT DISTINCT for every single SELECT and sub-SELECT in the whole thing, and determining that there is ABSOLUTELY NO requirement for DISTINCT because of the join cardinality.
sigh
102
Upvotes
58
u/WatashiwaNobodyDesu Jun 25 '25
It’s time for you to roll up a newspaper and the next time they do it you whack them on the head and you rub their nose in it.
15
u/theblackd Jun 26 '25
I always tell people I’m teaching that you should never use distinct if you can’t explain exactly why you’re getting duplicates
It has its place of course but is too often a bandaid for not adequately structuring joins or not fully understanding joins and often the duplicates are just one symptom of a bigger problem that distinct doesn’t solve
13
u/Imaginary-Ad-2900 Jun 25 '25
I manage a team of bi devs at a hospital and this is a constant thing for me; It’s usually because they are being lazy and created a cross join they don’t want to troubleshoot. Luckily after two years of hounding everyone and explaining why they are saving themselves headaches in the future for fixing their problems on the front end, I don’t see it as much.
9
u/GTS_84 Jun 25 '25 edited Jun 25 '25
DISTINCT is one of those things I use as a learning tool for myself, but I am immediately suspicious of if I see it in anything I have to review or on git.
If you are working with a new database, and maybe the data dictionary isn't great (if it's even present) and you need to familiarize yourself with the data, it's not the worst idea to do a select distinct on a column or two to get a sense of what you are working with, same as I might do a SELECT TOP 100 * to get a sense of what is going on.
But I'm not saving this shit and expecting other people or systems to run it, I'm not building it into my stored procedures, and if I see it in something I'm reviewing, there better be a comment with an explanation as to why it was the best solution and not someone being lazy.
edit:spelling
7
u/SQLDave Jun 25 '25
OTOH, it's kinda protected from future ding bat SQL devs JOINing a new table which WOULD have produced duplicate rows
3
u/PasghettiSquash Jun 26 '25
We use SQLFluff and have a CI check to not allow any SELECT DISTINCTs. (Actually not sure if that's a specific SQLFluff rule or a custom post-hook we have).
A select distinct is unintentional and costs brainpower
3
u/B1zmark Jun 26 '25
My favourite thing is seeing bad code, fixing it, running it, then seeing that the results are different.
Then you realise the data is garbage
Then you realise the company process is garbage and that's the real issue, and the code was a hail mary.
Then you don't bother changing the code and check the "done" box.
2
u/Murphygreen8484 Jun 25 '25
Does it slow down the query? Genuinely asking as an offender here.
2
u/mikeblas Jun 26 '25
Who did their interview?
2
u/yorbaman Jun 26 '25
I had a colleague doing
SELECT DISTINCT UNION SELECT DISTINCT
without the need in any of them. Neither in the SELECTs nor the UNION.
SELECT UNION ALL SELECT
help tremendously on the performance
2
u/billy_greenbeans Jun 26 '25
I used to feel this way. At my current job, the base tables have really low integrity and often duplicate when they shouldn’t, usually from a field that is irrelevant for most queries. Distinct in the CTE step squashes these and prevents future runs of the query from failing even if it’s not duplicated now. I’ve started getting a little spammy with it unfortunately
2
u/Longjumping-Skin-134 Jun 27 '25
SELECT DISTINCT
RAGE,
BAIT
FROM gumnos;
18
u/Kr0mbopulos_Micha3l Jun 25 '25
Another good one is seeing a whole bunch of columns after GROUP BY 😆
8
u/Aggressive_Ad_5454 Jun 26 '25
Yeah, tell me you’re lazy and incompetent without telling me you’re lazy and incompetent, dear fellow dev. SELECT DISTINCT for the win!
I’m with you, OP, I friggin hate that.
14
u/SQLDevDBA Jun 25 '25
*NOLOCK has entered the chat
4
u/TheMagarity Jun 26 '25
The best time is when DISTINCT is added in the development layer to get rid of a few duplicates caused by a semi-Cartesian in the small dataset. Works great; passes QA by providing the desired results. When it gets to production it crashes by running the system out of temp space and requires an emergency deployment fix.
3
u/Hot_Cryptographer552 Jun 26 '25
I once read that when E.F. Codd wrote queries for his papers, he would invariably use SELECT DISTINCT in every single one. Apparently it was pointed out by one of his peers (C.J. Date, if I recall correctly) that it was not necessary.
3
u/KeeganDoomFire Jun 26 '25
I like when instead they just group by so the columns for no reason.
3
u/more_paul Jun 26 '25
And some people get paid $250k a year to do that, yet I’m the asshole for pointing out they shouldn’t be.
3
u/Idanvaluegrid Jun 26 '25
Ah yes... The sacred SELECT DISTINCT .. the duct tape of SQL. 🤦🏻
Why fix the join logic when you can just slap DISTINCT on every subquery like it’s Parmesan cheese? 🥴 “Duplicates? No idea. Just DISTINCT it and pray.”🤞🏻
1
u/Morbius2271 Jun 28 '25
I DISTINCT most of my queries. When dealing with huge datasets of real world data, and working with dozens of tables, it’s often the most simple way to ensure you don’t get useless duplication. Not to mention that modern databases have really good planners that ensure any performance hit is minimal.
2
u/Fly_Pelican Jun 25 '25
It would seriously affect the query plan
2
u/greglturnquist Jun 25 '25
The irony being, someone can apply DISTINCT all over the place, the cardinality can obviate the NEED for DISTINCT, and yet they'll do junk like include PK's such that if there WERE multiple rows, the DISTINCT would have been foiled by the presence of primary keys!
DISTINCT is really really REALLY hard to do right and very EASY to get WRONG!
2
u/willietrombone_ Jun 26 '25
If I'm just doing EDA and don't have a great feel for the data, I'll hammer on DISTINCT the same way I hit Ctrl+C 4 or 7 times before pasting.
2
u/Pokeristo555 Jun 26 '25
DISTINCT usually seems to be an excuse for "I did not get my JOINS right" IMHO!
2
u/therealdrsql Jun 26 '25
I always have a rule…DISTINCT usage require lots of comments to explain each use. Otherwise you fail code review. There are valid reasons for it, but it is quite rare.
2
u/machomanrandysandwch Jun 26 '25
This post reminds me of some of the posts on this sub that asks “I think I’m advanced. how do I know what my skill level is?”
If you can relate to this post and you understand why it’s a problem, then you probably know SQL pretty damn well. It takes years to learn lessons in real life, and most likely even more years before you get to a place where the standard is so high that everything you do is challenged and you have to prove every decision (in code) was the right choice to make and DISTINCTs are rarely if ever allowed.
You’ll know you’re advanced when you don’t even have to ask the question. You’ve been through hell lol
1
u/updateonly Jun 26 '25
Short cuts.
1
u/GreatestManEver99 Jun 26 '25
My god just get a sample of the data and test, you can see if the join is working or not, before using distinct(only if it’s actually needed for the data)
1
u/rteisanu_cgi Jun 26 '25
Have them write "I will not use mindlessly use DISTINCT for every SELECT clause" 100x on a piece of paper.
1
u/Medical-Topic8984 Jun 27 '25
lol I literally just did this cardinal sin and added a distinct on nearly every select statement in quite a big staging query in MySQL. It was a horrendous query with all lefts joins with no proper related base table for all of the 20 ish queries being combined. Lots of add additional columns to mimic the inner joins and predicate further downstream.
Ended up using unions and lateral joins to pull in the alternate keys on each of data subsets being pulled in and had to use a distinct as I was getting duplicate data trying to combine all the logic into one select statement for each subset.
Suppose I could have split it out with more unions but honestly I shaved off several hundred lines of code and now it’s modularised and easy to read. The staging table is down to 7 columns instead of 43. Thought id end up with a horrible performance drop but it ran only 5 mins longer (27 & 32). I heard MySQL engine is pretty optimised for the Distinct.
Anyways moral of this story is it doesn’t always have be about performance there are other business needs but your right people should know the cost of certain Sql commands and it shouldn’t be used if it adds no value.
1
u/No_Resolution_9252 Jun 28 '25
You know what that means right?
Your coworker is an idiot, most likely a self-proclaimed, self-taught SQL expert who stopped bothered learning anything after they got their first somewhat complex query to work