SQL Anti-Patterns • Hacker News Board

Re: SQL Anti-Patterns

#141

Post by crazygringo » Sat, Oct 18, 2025, 9:44 PM UTC

MaxBarraclough wrote: post #129

crazygringo wrote: post #125

Earlier quoted context omitted.

Not sure what you mean. If you have a table of customers and someone of them don't have addresses, it's standard to leave the address fields NULL. If some of them don't belong to a company, it's standard to leave the company_id field NULL. This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common. If you're suggesting mandatory additional has_address and has_c…

> This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common. Kinda. You need null for outer joins, but you could have a relational DBMS that prohibits nullable columns in tables. Christopher Date thought that in properly normalised designs, tables should never use nullable columns. Codd disagreed. [0] > If you're suggesting mandatory additional has_address and…

> The way to do it without using a nullable column

I mean, you could, but having separate tables for every optional field would be an organizational and usability nightmare. Queries would be longer and slower for no good reason. Not to mention a gigantic waste of space with all those repeated primary keys and their indexes.

And you could have databases that prohibited NULL values, but we mostly don't, because they're so useful.

Re: SQL Anti-Patterns

#142

Post by crazygringo » Sat, Oct 18, 2025, 9:49 PM UTC

SoftTalker wrote: post #132

crazygringo wrote: post #125

Earlier quoted context omitted.

Not sure what you mean. If you have a table of customers and someone of them don't have addresses, it's standard to leave the address fields NULL. If some of them don't belong to a company, it's standard to leave the company_id field NULL. This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common. If you're suggesting mandatory additional has_address and has_c…

No null is fine if you don’t know or there’s literally no value. But don’t interpret a null phone number to mean the customer doesn’t have a phone number. You can’t infer anything from that, other than you don’t have it.

I'm not sure I agree.

If I have a column for the ID of the customer's current active subscription, and that column is NULL, it seems perfectly fine to interpret that the customer has no active subscription.

That is a valid inference. You don't need a separate has_active_subscription field.

On the other hand, your phone number example is just common sense. The database doesn't represent the external world. The database just knows the customer didn't provide a phone number.

Re: SQL Anti-Patterns

#143

Post by FuriouslyAdrift » Sat, Oct 18, 2025, 10:13 PM UTC

The single biggest thing that helped me speed up my queries and lower resource usage on the server was focusing on making my queries more sargable.

https://en.wikipedia.org/wiki/Sargable

https://www.brentozar.com/blitzcache/non-sargable-predicates...

Re: SQL Anti-Patterns

#144

Post by sgarland » Sat, Oct 18, 2025, 10:33 PM UTC

sigwinch28 wrote: post #38

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.

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?)

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

#145

Post by kpcyrd » Sat, Oct 18, 2025, 10:40 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.

SQL is somehow "ask two people, get three different opinions" for something as basic as:

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

Re: SQL Anti-Patterns

#146

Post by bambax » Sat, Oct 18, 2025, 11:13 PM UTC

> Excessive View Layer Stacking

Guilty as charged. I love to do this. Materialized views aren't really possible on sqlite, and so I find stacking views on top of one another very readable and manageable. But it's true other people find it a little obscure and weird.

Re: SQL Anti-Patterns

#147

Post by anotherevan » Sun, Oct 19, 2025, 12:20 AM UTC

em500 wrote: post #5

JohnHaugeland wrote: post #3

these aren’t anti patterns. these are just things you shouldn’t do

Still waiting for the definitive article on how using the term anti-pattern is an anti-pattern.

Well you do have to be careful, because if patterns and anti-patterns come into contact it could cause an explosive conflagration of regular expressions all over the place.

Re: SQL Anti-Patterns

#148

Post by virissimo » Sun, Oct 19, 2025, 12:32 AM UTC

sgarland wrote: post #144

sigwinch28 wrote: post #38

Earlier quoted context omitted.

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?)

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.

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

#149

Post by pbnjay » Sun, Oct 19, 2025, 12:33 AM UTC

sgarland wrote: post #144

sigwinch28 wrote: post #38

Earlier quoted context omitted.

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?)

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.

FYI this is not true in the US. Zip codes identify postal routes not locations

Re: SQL Anti-Patterns

#150

Post by sgarland » Sun, Oct 19, 2025, 12:36 AM UTC

dotancohen wrote: post #63

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.

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.