7 ms·

There's no good reason for using two different keywords for filtering and it harms composability. But of course SQL isn't very composable anyhow. Preferably, S

There's no good reason for using two different keywords for filtering and it harms composability. But of course SQL isn't very composable anyhow. Preferably, SQL would look more like: FROM Foo f WHERE f.value < 10 JOIN Bar b on b.id = f.bar_id WHERE b.other_value > 20 SELECT f.group, f.value GROUP BY f.group the_group AGGREGATING SUM(f.value) the_sum WHERE the_sum > 100 ORDER BY the_group And so on. FROM introduces a source of data which flows into the next line. SELECT does a projection, GROUP BY applies aggregation operators on groups and also does a projection. WHERE could be inserted pretty much anywhere.

goto11 5y ago
This is basically how Linq (in .net) does it. It is much more composable than SQL but still implement the relational algebra.
- wongarsu 5y ago
  And LINQ is converted into SQL if you do LINQ queries against a database. The queries are sometimes far from optimal, but the developer experience is quite nice.
Pxtl 5y ago
Yup. To be blunt: SQL is a flawed old language that should've been replaced decades ago, but every time somebody tries to replace it they throw the baby out with the bathwater and throw out the relational theory too. It was a fine language for its time. So was Pascal, and FORTRAN. We don't use those anymore generally for a variety of good reasons. SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. edit: I'm getting "You're posting too fast" so I can't continue this conversation, but no being able to express "unknown" is not worth throwing out the reflexive property of equality. That is a bad trade-off and I will die on this hill.
- mrbungie 5y ago
  It may be flawed, it may be old, but it clearly continues to be sufficiently expressive and useful. Every time someone tries to replace it, it eventually comes back again. Just look at the recent NoSQL and Big Data craze, and then how we're today with a miriad of SQL engines.
  - qsort 5y ago
    I think GP is correct when he says "every time somebody tries to replace it they throw the baby out with the bathwater and throw out the relational theory too". However, I would add that SQL is a very strong local optimum: a "good" language that keeps the good parts would be insufficiently different from actual SQL to convince anybody to switch. Also people forget that querying is only half of the equation, there's DDL too.
    - setr 5y ago
      DDL is a bit curious, because it’s less a programming language and more of a set of configuration and operational commands. It just happens to look similar to DQL, but there’s nothing really pushing that beyond the fact that DDL can rely on DQL as a subexpression (eg create view as …). You could probably say the relationship between DQL and DDL is largely aesthetic The only reason I can think of where you often can’t strictly treat DQL and DDL as totally different languages is the just from the total lack of interest in consistency in SQL language design generally — eg update and delete steal parts of DML syntax haphazardly and differently; the syntax for update/delete data referring to another table UPDATE table WHERE … JOIN … vs DELETE FROM table USING … The lack of complexity in DDL generally (its very wide, but not very deep; there’s not much meaningful composition to be discussed) and its lack of a real relationship to DQL is probably why no one discusses it much
  - deleted 5y ago
    [deleted]
- civilized 5y ago
  > SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. Nah. One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is". This is a fundamental feature needed in data management. So say you've got data on two balls A and B. You don't know what the color of either ball is. Is color(A) = color(B)? What's the best answer to this question? Obviously it's "we don't know", and the value we use to communicate "we don't know" is null. SQL nulls aren't broken. They're just a third option outside of True/False that needs its own handling, because reality is complicated. PS: Contrary to popular opinion in certain coding circles, floating point arithmetic isn't broken either. It's a beautiful, delicate tool that works the way it does because it must in order to support its use cases (e.g. numerical linear algebra and representing continuous physical quantities). If it doesn't work exactly the way you want, you may need a couple guardrails or a different system like fixed point arithmetic.
civilized 5y ago
If you want to see an incredibly successful data manipulation system that works exactly as you describe, you should check out R's dplyr package immediately. To me it's the perfect composable SQL API, but hardly anyone knows about it outside of R/RStudio/tidyverse aficionados! It also cross-compiles to SQL and can thus be used to build queries, although the result is not necessarily the most efficient. Someday I hope someone with expertise in compilation and query optimization will take a look at dplyr and figure out how to get it to generate optimally efficient SQL. If we did that, and also ported dplyr to some other languages, programmers might never have to hand-write SQL again. (LINQ might be a good way too, but I know much less about it)
- BoiledCabbage 5y ago
  If you like dplyr in R, you really should check out Linq. Dplyr as nice as it is doesn't remotely compare to how sensible and ergonomic Linq is.
  - civilized 5y ago
    I've heard! I don't have much occasion to use Linq as a data scientist, but will definitely keep an eye out if I ever write C#, etc.
- flakiness 5y ago
  Kind of sad Hadley Wickham isn't as prolific as a paper writer anymore. These papers [1] were, as its nature, very consumable even for someone outside R community. I liked them a lot. [1] https://scholar.google.com/citations?user=YA43PbsAAAAJ&hl=en&oi=ao https://scholar.google.com/citations?user=YA43PbsAAAAJ&hl=en...
  - civilized 5y ago
    Totally agree... but if I have to choose between him writing papers and spending more time perfecting APIs, there's no question. The man is one of the most brilliant interface designers ever to live. Dplyr was already great in its first version, but Hadley and company have worked incredibly hard to make it much, much better. It's hard to think of another system that has improved so much from such an already high starting point.
faho 5y ago
I would go one step further and make the join just JOIN Bar b if you've defined the foreign keys before. The need to spell them out again and again is one of my personal SQL pet peeves.
xi 5y ago
This is pretty close to how my Julia library [0] for composable construction of SQL queries works: From(foo) |> Where(Get.value .< 10) |> Join(From(bar) |> As(:bar), on = Get.bar.id .== Get.bar_id) |> Where(Get.bar.other_value .> 20) |> Select(Get.group, Get.value) |> Group(Get.group) |> Where(Agg.sum(Get.value) .> 100) |> Order(Get.group) There is no HAVING and the you can use any tabular operators in any order. Aggregates are also separated from grouping and can be used in any context after Group is applied. [0] https://github.com/MechanicalRabbit/FunSQL.jl https://github.com/MechanicalRabbit/FunSQL.jl
mamcx 5y ago
Building https://tablam.org https://tablam.org I learn a bit about the relational model and found that the whole having/group by is plain bad "hack". GROUP BY is totally the opposite of what you expect. It NOT "group by"!, This is group by: 1: A B 2: A C If where like this (as in the RM) then you can bet other hacks like graph-ql where not necessary (and will make database more friendly to OO langs)
- barrkel 5y ago
  GROUP BY is two things, partitioning and aggregation to a single row within each partition. The relational model doesn't really have a model for partitioning on its own so they're fused, but if they were distinct concepts it might make e.g. analytic window functions more composable and less awkward.

goto11 5y ago

This is basically how Linq (in .net) does it. It is much more composable than SQL but still implement the relational algebra.

wongarsu 5y ago
And LINQ is converted into SQL if you do LINQ queries against a database. The queries are sometimes far from optimal, but the developer experience is quite nice.

Pxtl 5y ago

Yup. To be blunt: SQL is a flawed old language that should've been replaced decades ago, but every time somebody tries to replace it they throw the baby out with the bathwater and throw out the relational theory too. It was a fine language for its time. So was Pascal, and FORTRAN. We don't use those anymore generally for a variety of good reasons. SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. edit: I'm getting "You're posting too fast" so I can't continue this conversation, but no being able to express "unknown" is not worth throwing out the reflexive property of equality. That is a bad trade-off and I will die on this hill.

mrbungie 5y ago
It may be flawed, it may be old, but it clearly continues to be sufficiently expressive and useful. Every time someone tries to replace it, it eventually comes back again. Just look at the recent NoSQL and Big Data craze, and then how we're today with a miriad of SQL engines.
- qsort 5y ago
  I think GP is correct when he says "every time somebody tries to replace it they throw the baby out with the bathwater and throw out the relational theory too". However, I would add that SQL is a very strong local optimum: a "good" language that keeps the good parts would be insufficiently different from actual SQL to convince anybody to switch. Also people forget that querying is only half of the equation, there's DDL too.
  - setr 5y ago
    DDL is a bit curious, because it’s less a programming language and more of a set of configuration and operational commands. It just happens to look similar to DQL, but there’s nothing really pushing that beyond the fact that DDL can rely on DQL as a subexpression (eg create view as …). You could probably say the relationship between DQL and DDL is largely aesthetic The only reason I can think of where you often can’t strictly treat DQL and DDL as totally different languages is the just from the total lack of interest in consistency in SQL language design generally — eg update and delete steal parts of DML syntax haphazardly and differently; the syntax for update/delete data referring to another table UPDATE table WHERE … JOIN … vs DELETE FROM table USING … The lack of complexity in DDL generally (its very wide, but not very deep; there’s not much meaningful composition to be discussed) and its lack of a real relationship to DQL is probably why no one discusses it much
- deleted 5y ago
  [deleted]
civilized 5y ago
> SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. Nah. One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is". This is a fundamental feature needed in data management. So say you've got data on two balls A and B. You don't know what the color of either ball is. Is color(A) = color(B)? What's the best answer to this question? Obviously it's "we don't know", and the value we use to communicate "we don't know" is null. SQL nulls aren't broken. They're just a third option outside of True/False that needs its own handling, because reality is complicated. PS: Contrary to popular opinion in certain coding circles, floating point arithmetic isn't broken either. It's a beautiful, delicate tool that works the way it does because it must in order to support its use cases (e.g. numerical linear algebra and representing continuous physical quantities). If it doesn't work exactly the way you want, you may need a couple guardrails or a different system like fixed point arithmetic.

civilized 5y ago

If you want to see an incredibly successful data manipulation system that works exactly as you describe, you should check out R's dplyr package immediately. To me it's the perfect composable SQL API, but hardly anyone knows about it outside of R/RStudio/tidyverse aficionados! It also cross-compiles to SQL and can thus be used to build queries, although the result is not necessarily the most efficient. Someday I hope someone with expertise in compilation and query optimization will take a look at dplyr and figure out how to get it to generate optimally efficient SQL. If we did that, and also ported dplyr to some other languages, programmers might never have to hand-write SQL again. (LINQ might be a good way too, but I know much less about it)

BoiledCabbage 5y ago
If you like dplyr in R, you really should check out Linq. Dplyr as nice as it is doesn't remotely compare to how sensible and ergonomic Linq is.
- civilized 5y ago
  I've heard! I don't have much occasion to use Linq as a data scientist, but will definitely keep an eye out if I ever write C#, etc.
flakiness 5y ago
Kind of sad Hadley Wickham isn't as prolific as a paper writer anymore. These papers [1] were, as its nature, very consumable even for someone outside R community. I liked them a lot. [1] https://scholar.google.com/citations?user=YA43PbsAAAAJ&hl=en&oi=ao https://scholar.google.com/citations?user=YA43PbsAAAAJ&hl=en...
- civilized 5y ago
  Totally agree... but if I have to choose between him writing papers and spending more time perfecting APIs, there's no question. The man is one of the most brilliant interface designers ever to live. Dplyr was already great in its first version, but Hadley and company have worked incredibly hard to make it much, much better. It's hard to think of another system that has improved so much from such an already high starting point.

faho 5y ago

I would go one step further and make the join just JOIN Bar b if you've defined the foreign keys before. The need to spell them out again and again is one of my personal SQL pet peeves.

xi 5y ago

This is pretty close to how my Julia library [0] for composable construction of SQL queries works: From(foo) |> Where(Get.value .< 10) |> Join(From(bar) |> As(:bar), on = Get.bar.id .== Get.bar_id) |> Where(Get.bar.other_value .> 20) |> Select(Get.group, Get.value) |> Group(Get.group) |> Where(Agg.sum(Get.value) .> 100) |> Order(Get.group) There is no HAVING and the you can use any tabular operators in any order. Aggregates are also separated from grouping and can be used in any context after Group is applied. [0] https://github.com/MechanicalRabbit/FunSQL.jl https://github.com/MechanicalRabbit/FunSQL.jl

mamcx 5y ago

Building https://tablam.org https://tablam.org I learn a bit about the relational model and found that the whole having/group by is plain bad "hack". GROUP BY is totally the opposite of what you expect. It NOT "group by"!, This is group by: 1: A B 2: A C If where like this (as in the RM) then you can bet other hacks like graph-ql where not necessary (and will make database more friendly to OO langs)

barrkel 5y ago
GROUP BY is two things, partitioning and aggregation to a single row within each partition. The relational model doesn't really have a model for partitioning on its own so they're fused, but if they were distinct concepts it might make e.g. analytic window functions more composable and less awkward.