[Izkata]

People have long joked about "yoda conditionals" ("if 5 == x", for example, instead of "if x == 5"), and flipping it in that order is the same thing: SELECT first is the same as "Get the fork from the drawer", where the flipped order actually sounds like something Yoda would say.

[Sankozi]

It is more like starting the recipe with "take sauce from point 6 and pour it over meat from point 10".

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.

[to11mtm]

What I did to get better at 'big' queries was start by writing out things like inner queries as CTEs, Table Parameters, or (if in oracle, lol) refCursors.

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.

[gwd]

a[5] means "From array a, select element 5", and nobody has a problem with that. If anything, the English habit of describing things in little-endian fashion ("Take a fork from the drawer to the left of the sink in the large kitchen") adds a lot of cognitive overhead because you have to wait for the end to figure out your first step, and then reverse the order. A much more practical way of writing that would be "In the large kitchen, to the left of the sink, there's a drawer; take the fork from the drawer."

[jbverschoor]

It's kind of nice to see which columns you're selecting, as they're always in the beginning

[gwd]

Right, that's a sort of counterpoint. It is nice to know that you're going to be getting a fork so you have context for the kitchen -> sink -> drawer instructions. Similarly, I expect in many cases, when you have a complex query, the fact that you know the result is going to be "userid, sum(score)", it makes the subsequent query easier to understand (as you know where it's got to end up). "Can you get me a fork? In the kitchen, next to the sink, in the drawer" might be even friendlier.

[goto11]

The question is if the syntax correspond to the logical order of operations.

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.

[ryathal]

SQL syntax is designed for reading, which is the majority of coding time. What gets selected is generally the thing most cared about so it goes first, where stuff comes from is the next most important so you get FROM and JOIN. Filtering and aggregation are next, because they are often hinted at by the select list anyway, then sorting is the least important so it comes at the end.

[naasking]

> What gets selected is generally the thing most cared about so it goes first

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.

[ryathal]

You know what you want to select at the time of writing, but 3 months later when someone else needs to add column foo the first two thoughts are: is it already in the select and is it available to the select without more joins. I've rarely ever touched filtering or aggregating lines in an exiting query unless requirements completely changed, which is less common than wanting more values added to an existing query.

[naasking]

I disagree. I've used C#'s LINQ extensively, where the select is at the end and adding columns is trivial. It's a complete non-issue.

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:

https://www.linqpad.net/

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

[goto11]

As long as you have each operation in the expected order, then SQL syntax might make sense. The problem comes when you need something outside this template, e.g. a projection after an aggregation. The straightforward syntax becomes unnecessary complex and hard to read if you just venture a bit outside of the default template.

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.