Hacker News Reader

Comment by richbell | Hacker News Reader

I would also like to see "friendly" SQL syntaxes like what DuckDB offers, but I doubt they'd add it without an update to the standards themselves.

https://duckdb.org/2022/05/04/friendlier-sql.html

View on HN

Oh thanks for sharing this! I love all of those, and most seem like they'd be easy sugar over the existing syntax. The biggest missing feature from those that I would really enjoy in data exploration tasks (though not in PROD) would be automatic JOIN ON selection based on foreign keys.

Example:

    SELECT users.id, COUNT(*)
    FROM users
    JOIN orders ON AUTO
    WHERE orders.created_at > NOW() - '7 day'::interval
    GROUP BY ALL

This would only work if there was an obvious path to do the join. In this case, I'm imagining that the `orders` table might have a `user_id` column which is a foreign key into the `users` table.

That sounds very close to NATURAL JOIN which is already present[0] although that does rely on the typical convention of FK columns being named the same on parent and child (related) tables.

I think you are suggesting some sort of lookup based on the defined FK relation, but that would be confused by situations where tables have multiple FK relations, such as tables with values restricted by a lookup value table (or more than 1 such FK). Those are pretty common, so I could see the 'AUTO' feature breaking down quickly. I think that is why the NATURAL JOIN approach is taken and that basically does what I believe you are describing, provided the column naming is matched.

[0] https://www.postgresql.org/docs/15/queries-table-expressions...

[edit:spelling]

You could use the name of the foreign key, together with FKs namespaced to the table they are on would allow very expressive joining, and the query might even survive schema changes. ORMs tend to work like this.

MySQL has done this for ages by using `NATURAL JOIN`.

Example pulled from Google: https://sebhastian.com/natural-join-mysql/

So does Postgres. Unbeknownst to the OP, supported it since at least v7.2 (aka a looooooong time ago).

https://www.postgresql.org/docs/7.2/queries-table-expression...

Worth to note that there is no innovation in DuckDB. The features such as:

    SELECT * EXCLUDE
    SELECT * REPLACE
    Column Aliases in WHERE / GROUP BY / HAVING
    Struct Dot Notation
    Function Aliases from Other Databases

are implemented in ClickHouse long before.

Reply on news.ycombinator.com