4 ms·

If the main complaint people have about SQL is that you can't swap SELECT, FROM and WHERE, then that's pretty good for a language designed in the 70s. This, by

by t8sr 3y ago

If the main complaint people have about SQL is that you can't swap SELECT, FROM and WHERE, then that's pretty good for a language designed in the 70s. This, by contrast, looks like it has a bunch of random line noise for syntax. Why on earth should I like this: `join side:left p=positions (p.id==employees.employee_id)` better than this: `LEFT JOIN positions AS p ON p.id = employees.employee_id` ?

kbenson 3y ago
Because now the type of join is an argument on the "join" operation, and join is the first word of that statement making it more obvious what the operation actually is? I also prefer foo(bar) over "bar to foo()" as well, which seems like the equivalent in a more general purpose function call in a language example.
lofatdairy 3y ago
Maybe it's my familiarity with R and the tidyverse paradigm, but I think in general this paradigm and syntax is pretty readable. The example you chose is perhaps a case where brackets and commas go a long way, but that aside it's really not too bad in context of the rest of the language. `:` seems to consistently be treated as keyword arguments, and `=` for aliasing.
jdmichal 3y ago
I've had two real gripes with SQL. The rest of it has been, as you said, pretty good. Complaint 1: Not being able to use selected columns later in the same select. SELECT gnarly_calculation AS some_value, some_value * 2 AS some_value_doubled Instead: SELECT subquery.*, some_value * 2 AS some_value_doubled FROM ( gnarly_calculation AS some_value ) AS subquery Complaint 2: Not being able to specify all columns except. This combines with the above, where I have to pull some intermediate calculations forward from a subquery, but I don't need them in the final output. So I have to then enumerate all the output columns that I actually want, instead of being able to say something like `* EXCEPT some_value`.
- snthpy 3y ago
  PRQL fixes this. (Disclaimer: I'm a PRQL contributor.)
  - zX41ZdbW 3y ago
    Not only PQRL fixes this. It has been allowed in ClickHouse's SQL since its inception.
    - snthpy 3y ago
      That's awesome! ClickHouse is a great system by all accounts and I've been meaning to try out ClickHouse Local. I'm more familiar with DuckDB and they've also been doing some great innovation on the SQL front. I don't know offhand if they can also do the forward referencing thing but they allow putting the FROM first and having GROUP BY ALL etc.. It's great to see all this innovation happening in the SQL and Query Language space more generally at the moment.
      - zX41ZdbW 3y ago
        It is strange to hear about innovation in DuckDB - I see that they are gradually re-implementing the stuff already existing in ClickHouse. Sometimes they do a better job at promoting it.
        snthpy 3y ago
        That's probably true but the big differentiator was that DuckDB can run in your python process so there's very low fiction to adopt it. My impression of ClickHouse was that it was more like postgresql in that regard, i.e. OLAP : OLTP as ClickHouse : Postgres as DuckDB : SQLite. clickhouse-local may have closed the gap on that though. Can you embed it in Python as a library?
        zX41ZdbW 3y ago
        > Can you embed it in Python as a library? https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb pip install chdb
crote 3y ago
The main issue with SQL is that you are stuck in a very strict way of writing things, which does not clearly match to how I think. The top-down way of writing PRQL where each step is simply a transformation of the previous one makes way more sense to me. SQL is something I'd need a reference manual for, PRQL is simply writing down what I want the query to do. I do agree that PRQL's join syntax is extremely bad, though. They should've stuck to explicit "left join"-like keywords, and the alias & join column shorthand could be done better.
- appplication 3y ago
  Despite it not at all being the right tool for most jobs, this is why I actually enjoy writing spark. It reads top down, and is “sql-ish” for most commands. Enough that you don’t need to go out of your way to learn it if you already know SQL, you just think in terms of serial transformations.
xchkr1337 3y ago
The problem with SQL is the same as the problem with C-style variable declarations. It sounds slightly better than the alternatives when you say the code out loud, but in reality it causes problems with readability and parsing/processing the code

by t8sr 3y ago

kbenson 3y ago

Because now the type of join is an argument on the "join" operation, and join is the first word of that statement making it more obvious what the operation actually is? I also prefer foo(bar) over "bar to foo()" as well, which seems like the equivalent in a more general purpose function call in a language example.

lofatdairy 3y ago

Maybe it's my familiarity with R and the tidyverse paradigm, but I think in general this paradigm and syntax is pretty readable. The example you chose is perhaps a case where brackets and commas go a long way, but that aside it's really not too bad in context of the rest of the language. `:` seems to consistently be treated as keyword arguments, and `=` for aliasing.

jdmichal 3y ago

I've had two real gripes with SQL. The rest of it has been, as you said, pretty good. Complaint 1: Not being able to use selected columns later in the same select. SELECT gnarly_calculation AS some_value, some_value * 2 AS some_value_doubled Instead: SELECT subquery.*, some_value * 2 AS some_value_doubled FROM ( gnarly_calculation AS some_value ) AS subquery Complaint 2: Not being able to specify all columns except. This combines with the above, where I have to pull some intermediate calculations forward from a subquery, but I don't need them in the final output. So I have to then enumerate all the output columns that I actually want, instead of being able to say something like `* EXCEPT some_value`.

snthpy 3y ago
PRQL fixes this. (Disclaimer: I'm a PRQL contributor.)
- zX41ZdbW 3y ago
  Not only PQRL fixes this. It has been allowed in ClickHouse's SQL since its inception.
  - snthpy 3y ago
    That's awesome! ClickHouse is a great system by all accounts and I've been meaning to try out ClickHouse Local. I'm more familiar with DuckDB and they've also been doing some great innovation on the SQL front. I don't know offhand if they can also do the forward referencing thing but they allow putting the FROM first and having GROUP BY ALL etc.. It's great to see all this innovation happening in the SQL and Query Language space more generally at the moment.
    - zX41ZdbW 3y ago
      It is strange to hear about innovation in DuckDB - I see that they are gradually re-implementing the stuff already existing in ClickHouse. Sometimes they do a better job at promoting it.
      - snthpy 3y ago
        That's probably true but the big differentiator was that DuckDB can run in your python process so there's very low fiction to adopt it. My impression of ClickHouse was that it was more like postgresql in that regard, i.e. OLAP : OLTP as ClickHouse : Postgres as DuckDB : SQLite. clickhouse-local may have closed the gap on that though. Can you embed it in Python as a library?
        zX41ZdbW 3y ago
        > Can you embed it in Python as a library? https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb pip install chdb

crote 3y ago

The main issue with SQL is that you are stuck in a very strict way of writing things, which does not clearly match to how I think. The top-down way of writing PRQL where each step is simply a transformation of the previous one makes way more sense to me. SQL is something I'd need a reference manual for, PRQL is simply writing down what I want the query to do. I do agree that PRQL's join syntax is extremely bad, though. They should've stuck to explicit "left join"-like keywords, and the alias & join column shorthand could be done better.

appplication 3y ago
Despite it not at all being the right tool for most jobs, this is why I actually enjoy writing spark. It reads top down, and is “sql-ish” for most commands. Enough that you don’t need to go out of your way to learn it if you already know SQL, you just think in terms of serial transformations.

xchkr1337 3y ago

The problem with SQL is the same as the problem with C-style variable declarations. It sounds slightly better than the alternatives when you say the code out loud, but in reality it causes problems with readability and parsing/processing the code