4 ms·

> I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structure

> I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes. That's one of the best things about SQL, it's declarative nature. I describe the end result, data I want to receive — not the instructions on how it should be done. There's no control flow, there's no program state, which means that my mental model of it is so much simpler.

lucianbr 2y ago
Yes but for performance you need to know how it is done, which defeats the declarative point. I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.
- sgarland 2y ago
  Sometimes you do, yes. Often times, though, the issue is that the statistics for the table are wrong, or the vacuum (for Postgres) hasn’t been able to finish. Both of these are administrative problems which can be dealt with by reading docs and applying the knowledge. I think of RDBMS like C: they’re massively capable and performant, but only if you know what you’re doing. They’re also very eager to catch everything on fire if you don’t.
- paulmd 2y ago
  > I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way. While this is doubtlessly true, in many cases the “rearranging” also involves a subtle change in what you are asking the database to do, in ways which allow the database to actually do less work. SELECT 1 WHERE EXISTS vs WHERE ID IN (SELECT ID FROM mytable WHERE …) is a great example. The former is a much simpler request despite functionally doing the same thing in the common use-cases.
  - sgarland 2y ago
    Yes, although both MySQL and Postgres will automatically rewrite the latter to the former, in most circumstances. I still find it good practice to explicitly write the semijoin, to demonstrate the intent. MySQL, but not Postgres, will also convert the inverse to an antijoin. As an aside, this is also often a great example to demonstrate why you shouldn’t use SELECT *, since you can’t write a semijoin if you need the predicate column in the result.
  - lucianbr 2y ago
    I don't see how that is supporting "SQL is declarative and therefore simple". You've kept the declarative part, but it's not simple anymore. You need to know multiple declarative ways to ask for the same thing, and the consequences of each. Might as well just tell the DB how you want to do it.

lucianbr 2y ago

Yes but for performance you need to know how it is done, which defeats the declarative point. I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.

sgarland 2y ago
Sometimes you do, yes. Often times, though, the issue is that the statistics for the table are wrong, or the vacuum (for Postgres) hasn’t been able to finish. Both of these are administrative problems which can be dealt with by reading docs and applying the knowledge. I think of RDBMS like C: they’re massively capable and performant, but only if you know what you’re doing. They’re also very eager to catch everything on fire if you don’t.
paulmd 2y ago
> I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way. While this is doubtlessly true, in many cases the “rearranging” also involves a subtle change in what you are asking the database to do, in ways which allow the database to actually do less work. SELECT 1 WHERE EXISTS vs WHERE ID IN (SELECT ID FROM mytable WHERE …) is a great example. The former is a much simpler request despite functionally doing the same thing in the common use-cases.
- sgarland 2y ago
  Yes, although both MySQL and Postgres will automatically rewrite the latter to the former, in most circumstances. I still find it good practice to explicitly write the semijoin, to demonstrate the intent. MySQL, but not Postgres, will also convert the inverse to an antijoin. As an aside, this is also often a great example to demonstrate why you shouldn’t use SELECT *, since you can’t write a semijoin if you need the predicate column in the result.
- lucianbr 2y ago
  I don't see how that is supporting "SQL is declarative and therefore simple". You've kept the declarative part, but it's not simple anymore. You need to know multiple declarative ways to ask for the same thing, and the consequences of each. Might as well just tell the DB how you want to do it.