Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.
An experienced person won't do that. For any moderately complex SQL query, before writing it I already have in mind the several jointures I'll need, since I usually know the tables and FK I'm working with. It's like following the edges of a graph, all in my head. But I don't know all the fields of these tables, so I rarely write their names from memory. So I have to write "SELECT 1 FROM …" and then go back to that "1" once my FROM is complete. That's not the end of the world, but it does smell.
I’m very experienced and do that kind of thing all the time.
Also, your “SELECT 1” technique seems like practically the same thing, except I guess you discover column names from autocomplete tooling rather than query output. (To my mind the difference is inconsequential.)
I typically keep all JOINs I've ever used on that schema in a single file, one per line.
Before writing a new query I can just copy paste some JOINs, simply skimming through table names like lego bricks.
That way it's surprisingly easy to beam from domain problem to a new query that uses 10 or 20 tables.
I've just realized that it might be all archaic now, in LLM era.
You have a library of useful joins that you've checked for correctness. Saving the library as reusable code would be even more useful.
But if you mean any other kind of experience or expertise, you are wrong. Those do not correlate with how a person assembles a query.
What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry.
My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.
I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language.
We're migrating from Sybase SQLAnywhere to MSSQL, and not being able to use aliases in WHERE, GROUP BY and ORDER BY is such a pain in the behind.
Almost all our non-trivial queries have to be nested multiple levels due to this, which doesn't exactly help readability.
My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance.
> The three window functions will also share the data layout, which will improve performance.
https://duckdb.org/docs/sql/window_functions#window-clauses
I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.
Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.
It's been an alternative in Postgres for decades.
TABLE foo
LIMIT 100;