3 ms·

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://duck

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 https://duckdb.org/2022/05/04/friendlier-sql.html

aftbit 4y ago
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.
- sroussey 4y ago
  MySQL has done this for ages by using `NATURAL JOIN`. Example pulled from Google: https://sebhastian.com/natural-join-mysql/ https://sebhastian.com/natural-join-mysql/
  - ttfkam 4y ago
    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-expressions.html https://www.postgresql.org/docs/7.2/queries-table-expression...
- discretion22 4y ago
  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.html#QUERIES-FROM https://www.postgresql.org/docs/15/queries-table-expressions... [edit:spelling]
- Rizz 4y ago
  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.
zX41ZdbW 4y ago
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.

aftbit 4y ago

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.

sroussey 4y ago
MySQL has done this for ages by using `NATURAL JOIN`. Example pulled from Google: https://sebhastian.com/natural-join-mysql/ https://sebhastian.com/natural-join-mysql/
- ttfkam 4y ago
  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-expressions.html https://www.postgresql.org/docs/7.2/queries-table-expression...
discretion22 4y ago
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.html#QUERIES-FROM https://www.postgresql.org/docs/15/queries-table-expressions... [edit:spelling]
Rizz 4y ago
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.

zX41ZdbW 4y ago

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.