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

jdmichal

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

PRQL fixes this.

(Disclaimer: I'm a PRQL contributor.)

zX41ZdbW

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

snthpy

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

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

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

> Can you embed it in Python as a library?

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

    pip install chdb