Very few people has problem with trivial queries like "SELECT x FROM y", but when query contains multiple joins or inner queries then having select at the beginning is visibly problematic.
That's (sometimes!) less performant than the one big query, but you can then refactor into a single query if you so choose.
Yeah, it's slow goings at first, but you get pretty good at SQL in the process.
In the query "SELECT foo + 2 FROM bar WHERE baz ORDER BY foo", the logical order is actually "FROM foo WHERE baz SELECT foo + 2 ORDER BY wawa" because of how each clause depends on the previous.
The SQL syntax is neither the logical order nor the direct reverse - it is just a random jumble.
I disagree. When you're reading a query you often already know what you were trying to select, you just have a problem with a clause somewhere. This suggests that the select should go last, and this makes perfect sense as Haskell's comprehensions and C#'s LINQ are both way easier to work with than SQL.
On the other hand having the select at the beginning has all kinds of problems for autocomplete, and syntactically obscures where you're selecting from and the clauses. I recommend you try LINQPad if you want experience with how much better this works:
> I've rarely ever touched filtering or aggregating lines in an exiting query unless requirements completely changed
Requirements change or bugs are discovered in the query. This is far more common than you imply.
SQL syntax is like if all arithmetic expressions had to be addition followed by subtraction followed by multiplication. And if you didn't need to add anything you would just have to add 0.
* The FROM clause: First, all data sources are defined and joined
* The WHERE clause: Then, data is filtered as early as possible
* The CONNECT BY clause: Then, data is traversed iteratively or recursively, to produce new tuples
* The GROUP BY clause: Then, data is reduced to groups, possibly producing new tuples if grouping functions like ROLLUP(), CUBE(), GROUPING SETS() are used
* The HAVING clause: Then, data is filtered again
* The SELECT clause: Only now, the projection is evaluated. In case of a SELECT DISTINCT statement, data is further reduced to remove duplicates
* The UNION clause: Optionally, the above is repeated for several UNION-connected subqueries. Unless this is a UNION ALL clause, data is further reduced to remove duplicates
* The ORDER BY clause: Now, all remaining tuples are ordered
* The LIMIT clause: Then, a paginating view is created for the ordered tuples
* The FOR clause: Transformation to XML or JSON
* The FOR UPDATE clause: Finally, pessimistic locking is applied
[1] https://www.jooq.org/doc/latest/manual/sql-building/sql-stat...
However I do also see the point of "SELECT first" just like a header you can infer the output data structure of a sub-expression without necessarily dive into the meat of it. It require a some brain training, but once you get there it oftentimes easier to navigate 100+ lines scripts by jumping from header to header (usually organized as CTE to make the code cleaner).
Maybe you’ve seen this thread already; a proposal with some alternatives on how to improve the situation for joining in foreign key columns, but in case not here is the link:
E.g. instead of the wrong `FROM a NATURAL JOIN b` you would use the correct `FROM a JOIN FOREIGN a.foo_fkey`, which not only needs that second name but now also loses the immediate naming of b. So e.g. autocomplete would have to look up the foreign key constraint to find out the second table. And it's still longer and harder to use than the natural join!
Most databases have one foreign key from a given table to another given table, and that simple case should be made easy to use.
https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cf...
So does the other way around in several SQL engines.
If you write something a long the lines of select x.ID, y.NAME from bla.bla as x join hum.hum as y on x.ID = y.FK in msSQL you’ll get autocomplete on x. and y..
You’re right that it’s more intuitive to write the from first of course.
So yes, you can autocomplete
SELECT employee.Na<TAB>
to "employee.Name", but it requires you to type the table name "employee." first.
But with the from-first style you can autocomplete even bare column names - you know you have "name" (possibly even "employee.name" and "supervisor.name") and "employeeID".
FROM this SELECT whatever
This already allows autocomplete for the attributes to work, and has an easier mental model - you think about the tables, then you think about their attributes. It also matches relational algebra better, where you'd do the projection (picking the attributes you want) at the end.
But anyway, simple cases being simple doesn't mean the language isn't horrible for more complex ones.
One thing I always complain about is join clauses making it easy to do the wrong thing (NATURAL JOIN) and annoying to do the correct thing (joining on the defined foreign keys).