Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Anti-Patterns

datamethods.substack.com

Post reply on HN
Print view View original

41–50 of 222 posts

Re: SQL Anti-Patterns

#41

Post by kijin »

Some of these things happen because people try to come up with a single clever query that does everything at once and returns a perfect spreadsheet.

Translating status codes into English or some other natural language? That's better done in the application, not the database. Maybe even leave it to the frontend if you have one. As a rule of thumb, any transformation that does not affect which rows are returned can be applied in another layer after those rows have been returned. Just because you know SQL doesn't mean you have to do everything in SQL.

Deeply nested subqueries? You might want to split that up into simpler queries. There's nothing shameful about throwing three stones to kill three birds, as long as you don't fall into the 1+N pattern. Whoever has to maintain your code will thank you for not trying to be too clever.

Also, a series of simple queries often run faster than a single large query, because there's a limit to how well the query planner can optimize an excessively complicated statement. With proper use of transactions, you shouldn't have to worry about the data changing under your feet as you make these queries.

Re: SQL Anti-Patterns

#42

Post by Sesse__ »

wvbdmp wrote: post #34
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.

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 dupli…

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

Re: SQL Anti-Patterns

#43

Post by sql_nitpicker »

mbb70 wrote: post #37
bandrami wrote: post #36

Earlier quoted context omitted.

IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know

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

distinct seems like an aggregation to me

Re: SQL Anti-Patterns

#44

Post by jmull »

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.

Re: SQL Anti-Patterns

#46

Post by 0xbadcafebee »

dgb23 wrote: post #19

If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change. Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

`select *` is bad for many reasons, but the biggest is that the "contract" your code has with the remote data store isn't immutable. The database can change, for many different reasons, independent of your code. If you want to write reliable code, you need to make as few assumptions as possible. One of those assumptions is what the remote schema is.

Re: SQL Anti-Patterns

#47

Post by crazygringo »

wmonk wrote: post #9

The section of using functions on indexes could do with more explicit and deeper explanation. When you use the function on the index it becomes a full scan of the data instead as the query runner has to run the function on every row and column, effectively removing any benefit of the index. Unfortunately I learned this the hard way!

The blog has a typo. The first line needs to have the text in uppercase:

> query WHERE name = ‘ABC’

> create an indexed UPPER(name) column

The point is that the index itself is already on the data with the function applied. So it's not a full scan, the way the original query was.

Of course, in this particular example you just want to use a case-insensitive collation to begin with. But the general concept is valid.

Re: SQL Anti-Patterns

#48

Post by ffsm8 »

egeozcan wrote: post #39

I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR reviews.

It's always perfectly aligned for me, because enter prefixes 2 whitespace in my ide in SQL files, ending with

    where a=1
      And k=2
      And v=3

Re: SQL Anti-Patterns

#49

Post by DrewADesign »

egeozcan wrote: post #39

I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR reviews.

> what I like to do is putting 1=1 after each WHERE to align ANDs nicely

Frankly, that sounds like one of those things that totally makes sense in the author’s head, but inconsiderately creates terrible code ergonomics and needless cognitive load for anyone reading it. You know to just ignore those expressions when you’re reading it because you wrote it and know they have no effect, but to a busy code reviewer, it’s annoying functionless clutter making their job more annoying. “Wait, that should do nothing… but does it actually do something hackish and ‘clever’ that they didn’t comment? Let’s think about this for a minute.” Use an editor with proper formatting capability, and don’t use executable expressions for formatting in code that other people look at.

Post reply on HN