SQL Anti-Patterns • Hacker News Board

Re: SQL Anti-Patterns

#61

Post by ryandv » Sat, Oct 18, 2025, 4:33 PM UTC

EvanAnderson wrote: post #14

> 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.

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

#62

Post by SoftTalker » Sat, Oct 18, 2025, 4:37 PM UTC

A big one that isn't listed is looking for stuff that isn't there.

Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already).

Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.

Re: SQL Anti-Patterns

#63

Post by dotancohen » Sat, Oct 18, 2025, 4:37 PM UTC

EvanAnderson wrote: post #14

> 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.

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

#64

Post by Sesse__ » Sat, Oct 18, 2025, 4:39 PM UTC

wvbdmp wrote: post #53

Sesse__ wrote: post #42

Earlier quoted context omitted.

The less verbose way of doing semijoins is by an IN subquery.

>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.

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

#65

Post by btilly » Sat, Oct 18, 2025, 4:41 PM UTC

The biggest SQL antipattern is failing to recognize that SQL is actually a programming language.

Therefore you should create a consistent indentation style for SQL. See https://bentilly.blogspot.com/2011/02/sql-formatting-style.h... for mine. Second, you should try to group logical things together. This is why people should move subqueries into common table expressions. And finally, don't be afraid of commenting wisely.

Re: SQL Anti-Patterns

#66

Post by magicalhippo » Sat, Oct 18, 2025, 4:42 PM UTC

SoftTalker wrote: post #62

A big one that isn't listed is looking for stuff that isn't there. Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already). Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.

> Also, understand how your DB handles nulls.

Also in regards to indexing. The DBs I've used have not indexed nulls, so a "WHERE col IS NULL" is inefficient even though "col" is indexed.

If that is the case and you really need it, have a computed column with a char(1) or bit indicating if "col" is NULL or not, and index that.

Re: SQL Anti-Patterns

#67

Post by geysersam » Sat, Oct 18, 2025, 4:47 PM UTC

SoftTalker wrote: post #62

A big one that isn't listed is looking for stuff that isn't there. Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already). Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.

> Using != or NOT IN (...) is almost always going to be inefficient.

Why do you say that?

My understanding is that as long as the RHS of NOT IN is constant (in the sense that it doesn't depend on the row) the condition is basically a hash table lookup, which is typically efficient if the lookup table is not massive.

What's the more efficient alternative?

Re: SQL Anti-Patterns

#68

Post by RHSeeger » Sat, Oct 18, 2025, 4:47 PM UTC

jmull wrote: post #44

EvanAnderson wrote: post #14

> 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.

I'd be wary of overgeneralizing on that. I guess it depends on whose queries you're usually reading.

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

#69

Post by dotancohen » Sat, Oct 18, 2025, 4:48 PM UTC

ryandv wrote: post #61

EvanAnderson wrote: post #14

> 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.

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`.

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

#70

Post by BuyMyBitcoins » Sat, Oct 18, 2025, 4:48 PM UTC

ryandv wrote: post #55

EvanAnderson wrote: post #14

> 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.

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.

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.