You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I've had (what I think is) a very clever idea to improve SQL (bare with me, I know that sounds mad 😱).
It's as simple as this: we move the "FROM" clause to the start of select statements, so:
SELECT foo, bar FROM spam becomes FROM spam SELECT foo, bar
SELECT * FROM my_table becomes FROM my_table SELECT *
SELECT x FROM my_table WHERE y=1 becomes FROM my_table SELECT x WHERE y=1
SELECT x FROM (select ...) as t becomes FROM (select ...) as t SELECT x
etc... you get the idea
This has two big advantages:
It's mean SQL language servers can be much much more helpful — it becomes possible to suggest column names in the SELECT clause because the user has already provided the table name — e.g. we can make sensible suggestions at FROM my_table SELECT a[CURSOR]
It's (arguably) easier to read - example: "We got roads, concrete, plumbing and calendars from the Romans" is quite hard to interpret the first time you read it because you need to keep all the things in your head without knowing the context, while "From the Romans we got roads, concrete, plumbing and calendars" is much easier to understand because you already have the context of the Romans when you read/hear the list. The same goes for FROM users SELECT id, name, created_at. The point is that SELECT ... FROM ... syntax follows some natural language which only puts the "from" at the end to developer suspense, at the cost of less easy understanding, we don't want suspense, we want ease
I also think it would make CTEs read much better: with t as (select 1 as a) from t select a since using t is directly after the CTE.
there's no new language to learn — it's still SQL with one small alteration
it's completely backwards compatible — SELECT foo, bar FROM spam still works fine, you just get to use FROM spam SELECT foo, bar if you like
no new parser is required, or decisions on how exactly to reinvent everythingelse, it's just an optional increase in flexibility in an existing parser, e.g. sqlparser-rs
So here's my feature request: an optional flag (allow_leading_from) which allows the FROM clause to come before SELECT. It can even be a feature so it's compiled out for those who don't want it.
Activity
adriangb commentedon Aug 26, 2024
See https://duckdb.org/2023/08/23/even-friendlier-sql.html#from-first-in-select-statements
samuelcolvin commentedon Aug 26, 2024
Oh nice, I had no idea.
samuelcolvin commentedon Aug 26, 2024
Thanks @adriangb, now I can just make the feature request "support duckdb 'from first' syntax".
samuelcolvin commentedon Aug 31, 2024
I have a branch that supports this, I'll create a PR when I have time.
Some related link
[-]Support "leading from" sytnax?[/-][+]`FROM` first in `SELECT` statements[/+]Support from first in select fix apache#1400
Support from first in select fix apache#1400
iffyio commentedon Mar 15, 2025
added in #1713