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

SQL Design Patterns (2010)

vadimtropashko.wordpress.com

Post reply on HN
Print view View original

21–30 of 35 posts

Re: SQL Design Patterns (2010)

#21

Post by halfcat »

sgarland wrote: post #19
paulddraper wrote: post #17

Earlier quoted context omitted.

SQL is a declarative language so it —- by definition —- hides the execution. Not really sure what you’re trying to argue here.

Parent made it sound - to me - that you put an input in and hope for the best. If you understand the operators, you can quite confidently predict an output given an input.

> If you understand the operators

That’s the point. In an imperative language if you don’t yet understand (or make a typo, or whatever), you can just print/console.log and find out.

I’ve seen junior devs, data analysts, and LLMs spin their wheels trying to figure out why adding a join isn’t producing the output they want. I don’t think they would figure it out using SQL alone if you gave them a month.

Re: SQL Design Patterns (2010)

#22

Post by paulddraper »

potatoproduct wrote: post #2

Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.

SELECT DISTINCT is often a code smell. (Not always.) If you see it, there’s a 70% chance it got slapped on to fix an issue that should have been solved a different way.

SELECT DISTINCT ON is different, and useful.

Re: SQL Design Patterns (2010)

#23

Post by crazygringo »

paulddraper wrote: post #17
sgarland wrote: post #15

Earlier quoted context omitted.

Frankly, this is terrible advice. If you’re not designing your data model around the language it’s going to be queried in, how do you expect to get decent performance out of the database? Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to, provided you understand what it is you asked it to do. The query engine is somewhat of a black box, but that is comp…

SQL is a declarative language so it —- by definition —- hides the execution. Not really sure what you’re trying to argue here.

You missed the "performance" part.

Depending on how you write your query and how you structure your data, a query can take 0.005 seconds or 500 seconds.

SQL hiding the execution is an extremely leaky abstraction. To get the performance you need, you have to plan your possible queries in advance together with how to structure the data.

I mean, it doesn't matter if you only have 100 rows per table, but once you're dealing with multiple tables with millions of rows each it's everything.

Re: SQL Design Patterns (2010)

#24

Post by belfthrow »

alphazard wrote: post #20
yakshaving_jgt wrote: post #12

Earlier quoted context omitted.

For posterity, how would you recommend the average working programmer should go about doing that?

An intro data structures course is worth watching if you haven't taken one. There are plenty of them on YouTube. Try to follow along with a language that has an explicit pointer type. Go is a good choice. Java and Python are worse choices (for this particular thing) IMO. Assuming you are familiar with trees and hashmaps, you have all the important building blocks. You can imagine a database as a bunch of trees, hashm…

Java is a bad language for this compared to go? Is this legitimate advice on a serious programming blog. Pretty unbelievable honestly.

Re: SQL Design Patterns (2010)

#25

Post by chasil »

sgarland wrote: post #15
alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

Frankly, this is terrible advice. If you’re not designing your data model around the language it’s going to be queried in, how do you expect to get decent performance out of the database? Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to, provided you understand what it is you asked it to do. The query engine is somewhat of a black box, but that is comp…

> Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to.

Ha ha, no, SQL implementations can conform to the standard in unexpected ways.

NULL = NULL

Is that true or false? We didn't know until 2003.

https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms

Re: SQL Design Patterns (2010)

#26

Post by gfody »

chasil wrote: post #25
sgarland wrote: post #15

Earlier quoted context omitted.

Frankly, this is terrible advice. If you’re not designing your data model around the language it’s going to be queried in, how do you expect to get decent performance out of the database? Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to, provided you understand what it is you asked it to do. The query engine is somewhat of a black box, but that is comp…

> Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to. Ha ha, no, SQL implementations can conform to the standard in unexpected ways. NULL = NULL Is that true or false? We didn't know until 2003. https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms

that's not true or false it's null

Re: SQL Design Patterns (2010)

#27

Post by baq »

alphazard wrote: post #7

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is…

You’re kinda right, but designing for a particular RDBMS with awareness of queries which will be performed thus indexes necessary (…or not ;) is really not that far away from what you propose. The only issue is beginner SQL learning material says ‘it’s declarative, don’t worry about what’s happening as long as you get a good result’ and that just isn’t true in any non-trivial applications of SQL.

Re: SQL Design Patterns (2010)

#28

Post by aspaviento »

paulddraper wrote: post #22
potatoproduct wrote: post #2

Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.

SELECT DISTINCT is often a code smell. (Not always.) If you see it, there’s a 70% chance it got slapped on to fix an issue that should have been solved a different way. SELECT DISTINCT ON is different, and useful.

I had a teacher who had specific rules for exams when we wrote SQL statements:

- For a question worth 2 points, if you use the word "DISTINCT" when it wasn't needed, you lose 0.5 points.

- If you don't use "DISTINCT" when it was necessary, you lose all 2 points.

Re: SQL Design Patterns (2010)

#29

Post by SkiFire13 »

halfcat wrote: post #21
sgarland wrote: post #19

Earlier quoted context omitted.

Parent made it sound - to me - that you put an input in and hope for the best. If you understand the operators, you can quite confidently predict an output given an input.

> If you understand the operators That’s the point. In an imperative language if you don’t yet understand (or make a typo, or whatever), you can just print/console.log and find out. I’ve seen junior devs, data analysts, and LLMs spin their wheels trying to figure out why adding a join isn’t producing the output they want. I don’t think they would figure it out using SQL alone if you gave them a month.

The equivalent of `print`/`console.log` in SQL would be using subqueries/CTE and run them to see the intermediate result (just like `print`/`console.log` show you intermediate results of the executions in an imperative language).

Re: SQL Design Patterns (2010)

#30

Post by teh_klev »

paulddraper wrote: post #17
sgarland wrote: post #15

Earlier quoted context omitted.

Frankly, this is terrible advice. If you’re not designing your data model around the language it’s going to be queried in, how do you expect to get decent performance out of the database? Also, in no way does SQL hide anything - it’s a declarative language, and will produce exactly what you tell it to, provided you understand what it is you asked it to do. The query engine is somewhat of a black box, but that is comp…

SQL is a declarative language so it —- by definition —- hides the execution. Not really sure what you’re trying to argue here.

>hides the execution.

But you're not prevented from finding out how your query was executed. For example EXPLAIN (MySQL, Postgres) or query analyser for MSSQL.

Post reply on HN