News for hackers. Stuff that matters.
Re:SQL Anti-Patterns You Should Avoid by EvanAnderson on Saturday October 18, 2025 @10:51AM (#45627850)
> Overusing DISTINCT to “Fix” Duplicates

Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.

Re:SQL Anti-Patterns You Should Avoid by sigwinch28 on Saturday October 18, 2025 @11:53AM (#45628287)
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)
Re:SQL Anti-Patterns You Should Avoid by valiant55 on Saturday October 18, 2025 @01:58PM (#45629203)
It depends when you see it, but I agree that DISTINCT shouldn't be used in production. If I'm writing a one off query and DISTINCT gets me over the finish line sparing me a few minutes then that's fine.
Re:SQL Anti-Patterns You Should Avoid by viraptor on Sunday October 19, 2025 @05:15AM (#45632957)
Which categories did the user post in? Which projects did the user interact with in the last week? That's all normal DISTINCT usage.
Re:SQL Anti-Patterns You Should Avoid by ndsipa_pomu on Sunday October 19, 2025 @05:45AM (#45633102)
There's nothing wrong with using DISTINCT correctly and it does belong in production. The author is complaining about developers that just put in DISTINCT as a matter of course rather than using it appropriately.
Re:SQL Anti-Patterns You Should Avoid by echelon on Saturday October 18, 2025 @04:35PM (#45630166)
DISTINCT, as well as the other aggregation functions, are fantastic for offline analytics queries. I find a lot of use for them in reporting, non-production code.
Re:SQL Anti-Patterns You Should Avoid by sgarland on Saturday October 18, 2025 @06:33PM (#45630820)
Because a city/region/state can be uniquely identified with a postal code (hell, in Ireland, the entire address is encapsulated in the postal code), but the reverse is not true.

At scale, repeated low-cardinality columns matter a great deal.

Re:SQL Anti-Patterns You Should Avoid by virissimo on Saturday October 18, 2025 @08:32PM (#45631316)
There are ZIP codes that overlap a city and also an unincorporated area. Furthermore, there are zip codes that overlap different states. A data model that renders these unrepresentable may come back to bite you.
Re:SQL Anti-Patterns You Should Avoid by pbnjay on Saturday October 18, 2025 @08:33PM (#45631325)
FYI this is not true in the US. Zip codes identify postal routes not locations
Re:SQL Anti-Patterns You Should Avoid by bdangubic on Saturday October 18, 2025 @09:55PM (#45631599)
saying zipcodes uniquely identify city/state/region is like saying John uniquely identifies a human :)
Re:SQL Anti-Patterns You Should Avoid by lucyjojo on Saturday October 18, 2025 @11:54PM (#45631980)
these kinds of things are almost never true in the real world.
Re:SQL Anti-Patterns You Should Avoid by sgarland on Sunday October 19, 2025 @08:09AM (#45633628)
EDIT: TIL that there are cross-state ZIP codes.
Re:SQL Anti-Patterns You Should Avoid by Breza on Monday October 27, 2025 @02:45PM (#45724766)
This assumption got me in trouble as a junior analyst years ago. I was asked to analyze our customer base and wrote something like the below. Management congratulated me on finding thousands more customers than we'd ever had before.

SELECT zipcode.rural_urban_code, COUNT(*) AS n_customer FROM customer INNER JOIN zipcode USING(zipcode) GROUP BY 1;

Re:SQL Anti-Patterns You Should Avoid by ndsipa_pomu on Sunday October 19, 2025 @05:43AM (#45633094)
One reason to have excessively normalised tables would be to ensure consistency so that you don't have to worry about various records with "London", "LONDON", "lindon" etc.
Re:SQL Anti-Patterns You Should Avoid by bts89 on Saturday October 18, 2025 @11:33AM (#45628145)
That’s almost always my experience too.

Though fairly recently I learned that even with all the correct joins in place, sometimes adding a DISTINCT within a CTE can dramatically increase performance. I assume there’s some optimizations the query planner can make when it’s been guaranteed record uniqueness.

Re:SQL Anti-Patterns You Should Avoid by yxhuvud on Sunday October 19, 2025 @04:06AM (#45632703)
I've seen similar effects when changing a bunch of left outer joins to lateral joins with a limit 1 tacked on. The limit do nothing to the end result, but speed up the query by a factor of 1000..
Re:SQL Anti-Patterns You Should Avoid by Breza on Monday October 27, 2025 @02:47PM (#45724789)
I agree with you. I also find that adding DISTINCT can sometimes make it easier for my colleagues to understand code, especially when I'm using multiple CTEs and it might be easy to miss a one-to-many join.
Re:SQL Anti-Patterns You Should Avoid by jmull on Saturday October 18, 2025 @12:00PM (#45628351)
I'd be wary of overgeneralizing on that. I guess it depends on whose queries you're usually reading.
Re:SQL Anti-Patterns You Should Avoid by RHSeeger on Saturday October 18, 2025 @12:47PM (#45628645)
I think you're reading more into what was said than is really there

> I immediately become suspicious

All I read from that is, when DISTINCT is used, it's worth taking a look to make sure the person in question understands the data/query; and isn't just "fixing" a broken query with it. That doesn't mean it's wrong, but it's a "smell", a "flag" saying pay attention.

Re:SQL Anti-Patterns You Should Avoid by bandrami on Saturday October 18, 2025 @11:47AM (#45628236)
IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know
Re:SQL Anti-Patterns You Should Avoid by mbb70 on Saturday October 18, 2025 @11:51AM (#45628274)
The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started
Re:SQL Anti-Patterns You Should Avoid by edoceo on Saturday October 18, 2025 @12:16PM (#45628435)
count(id) group by post_code order by 1
Re:SQL Anti-Patterns You Should Avoid by sql_nitpicker on Saturday October 18, 2025 @11:59AM (#45628346)
distinct seems like an aggregation to me
Re:SQL Anti-Patterns You Should Avoid by kristjansson on Saturday October 18, 2025 @12:24PM (#45628489)
Whole seconds will have been wasted!
Re:SQL Anti-Patterns You Should Avoid by wvbdmp on Saturday October 18, 2025 @12:26PM (#45628505)
Anti-Patterns You Should Avoid: overengineering for potential future requirements. Are there real-life cases where you should design with the future in mind? Yes. Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time? Also yes.
Re:SQL Anti-Patterns You Should Avoid by RHSeeger on Saturday October 18, 2025 @12:49PM (#45628665)
> Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time

Indeed, along that line, I would say that DISTINCT can be used to convey intent... and doing that in code is important.

- I want to know the zipcodes we have customers in - DISTINCT

- I want to know how many customers we have in each zipcode - aggregates

Can you do the first with the second? Sure.. but the first makes it clear what your goal is.

Re:SQL Anti-Patterns You Should Avoid by dleeftink on Saturday October 18, 2025 @02:51PM (#45629559)
Partly in jest, but maybe we need a NON-DISTINCT signaller to convey the inverse and return duplicate values only.

SOMEWHAT-DISTINCT with a fuzzy threshold would also be useful.

Re:SQL Anti-Patterns You Should Avoid by RHSeeger on Saturday October 18, 2025 @05:03PM (#45630326)
I hear you. It's not all _that_ uncommon for me to query for "things with more than one instance". Although, to be fair, it's more common for me to that when grep/sort/uniqing logs on the command line.
Re:SQL Anti-Patterns You Should Avoid by majormajor on Saturday October 18, 2025 @01:07PM (#45628796)
Here we start to get close to analytics sql vs application sql, and I think that's a whole separate beast itself with different patterns and anti-patterns.
Re:SQL Anti-Patterns You Should Avoid by bandrami on Saturday October 18, 2025 @05:04PM (#45630330)
Ah, yeah, you beat me to it. I do reporting, not applications.
Re:SQL Anti-Patterns You Should Avoid by bandrami on Saturday October 18, 2025 @05:02PM (#45630321)
I do reporting, not application development. If somebody wants to know different information I'd write a different query.
Re:SQL Anti-Patterns You Should Avoid by DavidWoof on Saturday October 18, 2025 @03:50PM (#45629926)
In OP's defense, "becoming suspicious" doesn't mean it's always wrong. I would definitely suggest an explaining comment if someone is using DISTINCT in a multi-column query.
Re:SQL Anti-Patterns You Should Avoid by Sesse__ on Saturday October 18, 2025 @10:54AM (#45627878)
Or just doesn't know how to do semijoins in SQL, since they don't follow the same syntax as normal joins for whatever historical reason.
Re:SQL Anti-Patterns You Should Avoid by wvbdmp on Saturday October 18, 2025 @11:41AM (#45628202)
Eh, sometimes you need a quick fix and it’s just extremely concise and readable. I’ll take an INNER JOIN over EXISTS (nice but insanely verbose) or CROSS APPLY (nice but slow) almost every time. Obviously you have to know what you’re dealing with, and I’m mostly talking about reporting, not perf critical application code.

Distinct is also easily explained to users, who are probably familiar with Excel’s “remove duplicate rows”.

It can also be great for exploring unfamiliar databases. I ask applicants to find stuff in a database they would never see by scrolling, and you’d be surprised how many don’t find it.

Re:SQL Anti-Patterns You Should Avoid by Sesse__ on Saturday October 18, 2025 @11:59AM (#45628345)
The less verbose way of doing semijoins is by an IN subquery.
Re:SQL Anti-Patterns You Should Avoid by wvbdmp on Saturday October 18, 2025 @12:23PM (#45628485)
>subquery

>less verbose

Well…

In any case, it depends. OP nicely guarded himself by writing “overusing”, so at that point his pro-tip is just a tautology and we are in agreement: not every use of DISTINCT is an immediate smell.

Re:SQL Anti-Patterns You Should Avoid by Sesse__ on Saturday October 18, 2025 @12:39PM (#45628588)
What do you mean? Here are your real alternatives for doing a semijoin (assuming ANSI SQL, no vendor extensions):

  SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE t2.x = t1.x );
  SELECT * FROM t1 WHERE x IN ( SELECT x FROM t2 );
  SELECT * FROM t1 JOIN ( SELECT DISTINCT x FROM t2 ) s1 USING (x);
Now tell me which one of these is the less verbose semijoin?

You could argue that you could fake a semijoin using

  SELECT DISTINCT * FROM t1 JOIN t2 USING (x);
or

  SELECT * FROM t1 JOIN t2 USING (x) GROUP BY t1.*;
but it doesn't give the same result if t1 has duplicate rows, or if there is more than one t2 matching t1. (You can try to fudge it by replacing * with something else, in which case the problem just moves around, since “duplicate rows” will mean something else.)
Re:SQL Anti-Patterns You Should Avoid by wvbdmp on Saturday October 18, 2025 @01:34PM (#45629017)
No, sorry, you’re certainly correct, I just meant that any subqueries are generally crazy verbose. And then you usually want additional Where clauses or even Joins in there, and it starts to stop looking like a Where clause, so I’m often happy when I can push that logic into From.
Re:SQL Anti-Patterns You Should Avoid by Sesse__ on Saturday October 18, 2025 @01:38PM (#45629043)
Yes, I would certainly prefer if you could write

SELECT * FROM t1 SEMIJOIN t2 USING (x);

although it creates some extra problems for the join optimizer.

Re:SQL Anti-Patterns You Should Avoid by Little_Kitty on Saturday October 18, 2025 @04:04PM (#45630005)
It's great being able to use an any join (and the counterpart anti join) in Clickhouse to deal with these operations.
Re:SQL Anti-Patterns You Should Avoid by leptons on Saturday October 18, 2025 @10:58AM (#45627909)
And that's okay. Not every developer knows every single thing there is to know about every single tech. Sometimes you just need a solution, and someone with more specific knowledge can optimize later. How many non-database related mistakes would you make if you had to build every part of a system yourself?
Re:SQL Anti-Patterns You Should Avoid by pessimizer on Saturday October 18, 2025 @12:29PM (#45628529)
But what if they don't know that they need your approval not to know things?
Re:SQL Anti-Patterns You Should Avoid by ryandv on Saturday October 18, 2025 @12:25PM (#45628498)
Set theory...

There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.

Re:SQL Anti-Patterns You Should Avoid by BuyMyBitcoins on Saturday October 18, 2025 @12:48PM (#45628654)
I am surprised at common it is for software engineers to not treat booleans properly. I can’t tell you how many times if seen ‘if(IsFoo(X) != false)’

It never used to bug me as a junior dev, but once a peer pointed this out it became impossible for me to ignore.

Re:SQL Anti-Patterns You Should Avoid by furyofantares on Saturday October 18, 2025 @03:31PM (#45629809)
The most egregious one I saw, I was tracking down a bug and found code like this:

    bool x;

    ...

    if (x == true) {
        DoThing1();
    } else if (x == false) {
        DoThing2();
    }
And of course neither branch was hit, because this is C, and the uninitialized x was neither 0 nor 1, but some other random value.
Re:SQL Anti-Patterns You Should Avoid by tomjakubowski on Saturday October 18, 2025 @04:42PM (#45630208)
Sometimes this kind of thing happens after a few revisions of code, where in earlier versions the structure of the code made more sense: maybe several conditions which were tested and then, due to changing requirements, they coalesced into something which now reads as nonsense.

When making a code change which touches a lot of places, it's not always obvious to "zoom out" and read the surrounding context to see if the structure of the code can be updated. The developer may be chewing through a grep list of a few dozen locations that need to be changed.

Re:SQL Anti-Patterns You Should Avoid by Rexxar on Sunday October 19, 2025 @05:34AM (#45633049)
Maybe it was initially supposed to be a sort of "3-value boolean" (true/false/undefined) and not a standard bool. You can (rarely) meet this pattern in c++ if you use boost::tribool or in c# if you have a nullable bool. There is probably similar thing in other languages.
Re:SQL Anti-Patterns You Should Avoid by furyofantares on Sunday October 19, 2025 @01:57PM (#45636319)
It was definitely just bad code.
Re:SQL Anti-Patterns You Should Avoid by munchlax on Saturday October 18, 2025 @02:45PM (#45629512)
People do that? This hurts my brain. if(IsFoo(X)) is clear and readable.
Re:SQL Anti-Patterns You Should Avoid by catlifeonmars on Saturday October 18, 2025 @02:19PM (#45629346)
Clearly the correct spelling is

`if(X&IsFooMask != 0)`

:)

Re:SQL Anti-Patterns You Should Avoid by 1718627440 on Sunday October 19, 2025 @04:55PM (#45637894)
I think of comparisons as a type conversion to a boolean. You wouldn't convert a boolean, but I like it to convert other types like an integer, even when the language rules would already specify the same I'm writing.
Re:SQL Anti-Patterns You Should Avoid by hyperman1 on Saturday October 18, 2025 @01:33PM (#45629004)
I've spent a lot of time not seeing how xor is just the 'not equals' operator for booleans.
Re:SQL Anti-Patterns You Should Avoid by layer8 on Saturday October 18, 2025 @01:28PM (#45628975)
Or, for a boolean type, that XOR is the same as the inequality operator.
Re:SQL Anti-Patterns You Should Avoid by avalys on Saturday October 18, 2025 @02:38PM (#45629473)
Maybe it’s confusing because it’s misnamed?
Re:SQL Anti-Patterns You Should Avoid by ryandv on Saturday October 18, 2025 @02:56PM (#45629590)
This is like saying the non-negative integers under addition, lists under append, and strings under concatenation are all just misnamings of the semigroup operator.

https://hackage.haskell.org/package/base-4.21.0.0/docs/Data-...

Re:SQL Anti-Patterns You Should Avoid by layer8 on Saturday October 18, 2025 @05:16PM (#45630402)
Is it? Two things are equal exactly when they aren’t exclusive.
Re:SQL Anti-Patterns You Should Avoid by catlifeonmars on Saturday October 18, 2025 @02:23PM (#45629374)
XOR is for key splitting.
Re:SQL Anti-Patterns You Should Avoid by dragonwriter on Saturday October 18, 2025 @12:30PM (#45628537)
In my experience, its nearly as often a problem with the design of the database as the query author.
Re:SQL Anti-Patterns You Should Avoid by ryandv on Saturday October 18, 2025 @12:33PM (#45628561)
PostgreSQL's `DISTINCT ON` extension is useful for navigating bitemporal data in which I want, for example, the latest recorded version of an entry, for each day of the year.

There are few other legitimate use cases of the regular `DISTINCT` that I have seen, other than the typical one-off `SELECT DISTINCT(foo) FROM bar`.

Re:SQL Anti-Patterns You Should Avoid by dotancohen on Saturday October 18, 2025 @12:48PM (#45628652)
Without DISTINCT ON (which I've never used) you can use a window function via the OVER clause with PARTITION BY. I'm pretty sure that's standard SQL.
Re:SQL Anti-Patterns You Should Avoid by ryandv on Saturday October 18, 2025 @12:49PM (#45628656)
Yes, this is the implementation I have seen in other dialects.
Re:SQL Anti-Patterns You Should Avoid by dotancohen on Saturday October 18, 2025 @12:37PM (#45628583)
I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tables (at least in sqlite, mysql, and maybe postgress too) the database will continue to search the entire table after the given record was found.
Re:SQL Anti-Patterns You Should Avoid by Guillaume86 on Saturday October 18, 2025 @01:57PM (#45629197)
Only if your table is missing an unique index on that column, which it should have to enforce your assumption, so yeah LIMIT 1 is a code (or schema in the case) smell.
Re:SQL Anti-Patterns You Should Avoid by dotancohen on Saturday October 18, 2025 @02:57PM (#45629591)
IDs are typically unique primary key. But in my experience, adding LIMIT 1 would on average halve the time taken to retrieve the record.

I'll test again, really the last time I tested that was two decades ago.

Re:SQL Anti-Patterns You Should Avoid by EvanAnderson on Saturday October 18, 2025 @03:40PM (#45629878)
That seems like your RDBMS wasn't handling something right there or there wasn't a unique index on the column.

Do you recall what the database server was?

Re:SQL Anti-Patterns You Should Avoid by dotancohen on Saturday October 18, 2025 @05:17PM (#45630410)
Yes, I was using Mysql exclusively at the time. I don't recall which version.

I also tested this once years later when doing a Python app with sqlite. Similar result, but admittedly that was not a very big table to begin with.

I am meticulous with my database schemas, and periodically review my indexes and covering indexes. I'm no DBA, but I believe that the database is the only real value a codebase has, other than maybe a novel method here and there. So I put care into designing it properly and testing my assumptions.

Re:SQL Anti-Patterns You Should Avoid by buckle8017 on Saturday October 18, 2025 @03:22PM (#45629744)
You are certainly doing something wrong if that's true.

I'm curious, can you demo this?

Re:SQL Anti-Patterns You Should Avoid by dotancohen on Saturday October 18, 2025 @05:21PM (#45630442)
I'm curious as well to see if this still holds up. I'll try this week.
Re:SQL Anti-Patterns You Should Avoid by viraptor on Sunday October 19, 2025 @05:17AM (#45632964)
That would be a reportable bug. Of a pretty high priority.
Re:SQL Anti-Patterns You Should Avoid by Guillaume86 on Sunday October 19, 2025 @11:20AM (#45634828)
You should use the DB explain or equivalent command to spit out the query plan, limit 1 shouldn't change anything in your case, if it's not the case you should file an issue, it's pretty much 101 of query optimization.
Re:SQL Anti-Patterns You Should Avoid by giovannibonetti on Saturday October 18, 2025 @04:55PM (#45630281)
I've noticed that LIMIT 1 makes a huge difference when working with LATERAL JOINs in Postgres, even when the WHERE condition has a unique constraint.
Re:SQL Anti-Patterns You Should Avoid by sgarland on Saturday October 18, 2025 @08:36PM (#45631337)
If you include an ORDER BY, the DB _may_ continue searching. MySQL (and, I assume, MS SQL Server, since it also can cluster the PK) can stop early in some circumstances.

But if you just have a LIMIT, then no - any RDBMS should stop as soon as it’s reached your requested limit.

Re:SQL Anti-Patterns You Should Avoid by dotancohen on Saturday October 18, 2025 @10:06PM (#45631627)
Right, that's why I add it.
Re:SQL Anti-Patterns You Should Avoid by fipar on Saturday October 18, 2025 @11:49PM (#45631971)
In mysql, the db will continue reading even if the limit condition has been met, and then anything beyond the limit will be discarded before returning the result.
Re:SQL Anti-Patterns You Should Avoid by dotancohen on Monday October 20, 2025 @07:13AM (#45642597)
Even without an ORDER BY clause?
Re:SQL Anti-Patterns You Should Avoid by fipar on Monday October 20, 2025 @10:04PM (#45651662)
Nope, that does work as expected, unless a filesort is required, good point.
Re:SQL Anti-Patterns You Should Avoid by 9rx on Saturday October 18, 2025 @01:38PM (#45629046)
Or believe more in Codd’s relational model than SQL’s tabulational model.
Re:SQL Anti-Patterns You Should Avoid by mcv on Saturday October 18, 2025 @01:51PM (#45629146)
It's the exact opposite in Cypher. I'm currently working with some complex data in neo4j, and wondered why my perfectly fine looking queries were so slow, until I remembered to use DISTINCT. It's very easy to get duplicate nodes in your results, especially when you use variable length relationships, and DISTINCT is the only fix I'm aware of that fixes that.
Re:SQL Anti-Patterns You Should Avoid by dleeftink on Saturday October 18, 2025 @02:44PM (#45629509)
Yeah, similarly combining distinct with recursive CTE's in SQL can be the difference between a n×n blowout or a performant graph walk that only visits nodes once.
Re:SQL Anti-Patterns You Should Avoid by ch2026 on Saturday October 18, 2025 @02:53PM (#45629575)
Or maybe they’re on OLAP not OLTP.
Re:SQL Anti-Patterns You Should Avoid by kpcyrd on Saturday October 18, 2025 @06:40PM (#45630868)
SQL is somehow "ask two people, get three different opinions" for something as basic as:

"given a BTreeMap<String, Vec<String>>, how do I do .keys() and .len()".

Re:SQL Anti-Patterns You Should Avoid by qcnguy on Sunday October 19, 2025 @05:36AM (#45633067)
SQL isn't very intuitive. Lots of people claim it is but then lots of people claim Haskell is, market outcomes suggest they are outliers.

The big justification for its design is to enable compiler optimizations (query planning) but compilers can optimize imperative code very well too, so I wonder if you could get the same benefits with a language that's less declarative.

Re:SQL Anti-Patterns You Should Avoid by fipar on Saturday October 18, 2025 @11:48PM (#45631963)
I'm not sure I understand the part about set theory. If anything, a valid use of DISTINCT is if you want the result to be (closer to) a set, as otherwise (to your point, depending on the data model) you may get a bag instead.

In fact, IIRC, using DISTINCT (usually bad for performance, btw) is an SQL advice by CJ Date in https://www.oreilly.com/library/view/sql-and-relational/9781...

Re:SQL Anti-Patterns You Should Avoid by grumpylittleted on Sunday October 19, 2025 @07:42AM (#45633520)
So how do you "know" when you can safely omit DISTINCT for your shiny new query SELECT x FROM t ?

Oh you looked the schema for t and it said x has a PRIMARY or UNIQUE constraint?

Ah well two minutes after you looked at the schema Tom removed the UNIQUE constraint. Now your scratching your head when you get duplicates.

Sql is a bag language not a set language. The contract with relation t is that if the runtime can find there rel t and attribute x it will return it. You may end up with rows or not, and you may end up with duplicates or not, and the type of x may change between subsequent execution.

So if you want a set you need to say so using DISTINCT. At runtime the query planner will check the schema and if the attribute is UNIQUE or PRIMARY it will not have to do a deduplication.