FROM first in SELECT statements

[samuelcolvin]

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:

1. 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]
2. 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.

Unlike alternatives to SQL like PRQL and EdgeQL:

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

WDYT?

[adriangb]

See https://duckdb.org/2023/08/23/even-friendlier-sql.html#from-first-in-select-statements

[samuelcolvin]

Oh nice, I had no idea.

[samuelcolvin]

Thanks @adriangb, now I can just make the feature request "support duckdb 'from first' syntax".

[samuelcolvin]

I have a branch that supports this, I'll create a PR when I have time.

Some related link

• HN thread on similar issue
• PR adding this to DuckDB Optionally allow for queries to start with FROM, instead of with SELECT duckdb/duckdb#5076