Hacker News Reader

Comment by t8sr | Hacker News Reader

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

View on HN

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.

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.

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`.

PRQL fixes this.

(Disclaimer: I'm a PRQL contributor.)

Not only PQRL fixes this. It has been allowed in ClickHouse's SQL since its inception.

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.

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.

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?

> Can you embed it in Python as a library?

https://github.com/chdb-io/chdb

    pip install chdb

They also do bunch of stuff that ClickHouse can't do, like correlated subqueries [1] or recursive CTEs.

I kinda wish for a database with ClickHouse storage and DuckDB optimizer. At least my experience with ClickHouse is that MergeTree is incredibly good at what it does, but the optimizer hurts.

[1]: https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.h...

Both complaints are resolved by ClickHouse.

This video also covers many other advantages of ClickHouse's SQL dialect: https://www.youtube.com/watch?v=zhrOYQpgvkk

Some may find your first complaint questionable... But I specifically designed ClickHouse SQL to allow aliases to be used and referenced in every part of SQL query.

So unsurprisingly based on my gripes, we are an OLAP shop. I've never heard of ClickHouse before, and I'll be looking into it. We're doing OK on PostgreSQL, but just OK. A lot of learning and unlearning.

Don't use *. It is a common source for all kinds of problems. Many query langs does not support it at all for that reason.

I'm quite aware of your advice regarding using `` against table. However, I'm talking about using `` against subqueries and CTEs where the pieces of the table have already been extracted.

This is supposed to read:

I'm quite aware of your advice regarding using `*` against tables. However, I'm talking about using `*` against subqueries and CTEs where the pieces of the table have already been extracted.

I was unable to edit this earlier due to HN being unavailable after I realized the formatter mangled it.

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.

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.

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

Reply on news.ycombinator.com