I agree except one thing: SELECT cols FROM table. This should really be FROM table SELECT cols as every modern SQL editor will then be able to show you a list of available columns.

See LINQ. There they had the opportunity to fix it and it works just great.

More generally, statements should appear in the same order as they are executed, as far as this is possible.

This nullifies some other common sources of confusion, such as which statements are executed before a GROUP BY and which ones after (and why HAVING exists as a keyword). It's also similar to how CTE syntax is generally much more readable than subqueries.

The order of clauses in a SQL statement should have no relation on the order of execution because SQL is a declarative query language. The query specifies what the user wants and not how the system should compute the answer.

This declarative property also provides a (somewhat weak) motivation of why SELECT should come first. Precisely because it answers part of the WHAT, i.e., the schema of the result.

I personally think first about the loop/iterator organization (FROM in SQL) and only after that what I am going to do with its elements.

However,

    SELECT a1,a2,... FROM Table AS t WHERE Condition

is syntactically equivalent to Python list comprehension:

    [(t.a1,t.a2,...) for t in Table if Condition]

Here the use of attributes (SELECT) is also written before the iterator.

SELECT is also analogous to normal loops:

    foreach t in Table
      if not Condition: continue
      # Use t.a1, t.a2 etc.

Here we first provide the loop specification while the usage of elements is written only in the body.

I concur. I always start with select * from table because I cannot possibly remember all the right column names

In the clients I've used (SQLYog, Mysql Workbench), aliasing a table lets you use autocomplete when referencing that alias followed by a dot.

So, I will start with a SELECT * FROM myTable t, then go back and replace * with t.<columns appear here>.

I'll use the same in other places in the query like WHERE conditions.

I mean, it works either way. Once you type the column names, it can guess the table name easily.

I use DataGrip at work and it can complete the column names without knowing the table name, so it ends up not being a problem.

> Once you type the column names, it can guess the table name easily.

If the query is simple enough that a natural join would work and you aren't doing FROM clause aliasing (which can be useful to make reusable queries self-documenting), sure.

For more complex queries and obviously any time table or column aliasing are used, that becomes somewhere between less likely and logically impossible.

I'd rather have the enjoyment of being able to think in normal English speak rather than Yoda speak just to fix one edge case that appeared decades after they came up with the syntax.

It’s also how azure query language works.

I teach SQL to financial people and, for the most part, it isn't too bad. When I get to joins, I teach a subset of SQL where all joins are done through INNER JOIN / LEFT JOIN syntax and that helps a lot. Joins are the part that everyone has trouble with.

Tbh I usually see teachers worried about joins but its the group by/having clauses that I always watched people struggle with (at least, writing it).

Ofc that might just be the emphasis on joins makes group by the next lowest hanging fruit

What do your abbreviations mean?

tbh = to be honest, ofc = of course

Urban Dictionary (https://www.urbandictionary.com) or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations of unknown abbreviations, in my experience.

> or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations

I mean, that's what they did.

> Joins are the part that everyone has trouble with.

There is significant problem-solution mismatch in joins and some other SQL constructs which are therefore semantically quite controversial in many use cases:

https://github.com/asavinov/bistro#why-column-orientation

The syntax isn't the problem though. Join relational operation is though on itself.

Can you explain when to really use a lateral join?

In Postgresql, Lateral Joins enable function chaining for set returning functions. ie you can feed the results of earlier srf function to the arguments of following function. This gets around one of Postgresql most glaring omissions - table valued parameters. Is incredibly useful once you get used to them.

I might be misunderstanding your point, but Postgresql functions can take (and return) tables:

    CREATE FUNCTION fullname(person) RETURNS TEXT AS $$
    SELECT $1.firstname || ' ' || $1.lastname
    $$ LANGUAGE SQL

Could you go into a more detail wrt using lateral joins with set returning functions? I'm used to using them as a way to "extend" the row with later terms depending on prior terms as you say, but I'm not comprehending the srf case so well (or tbh the results that pop up when I search for "posrgresql set returning function").

In my experience it’s relatively rare that you need them. I think of them as the for-each of joins. It evaluates row by row, not a single time. Another analogy would be a correlated subquery.

I write a lot of sql and it is generally a nice language.

But for my taste the language is to close to English and therefore redundant and inconsistent in an annoying way. Example:

Select x from tbl ...

Delete from tbl ...

Update tbl set x=...

Why change the word order?

Yes,I also suggest standards committee allow the option of putting FROM clause first - this would make it easier for beginners to grok what’s going on and enable better tooling such as auto-complete.

On PC the alternatives were xBase, FoxPro, Paradox and Access, so it was a no brainer that SQL eventually took over.

Since there weren't any commercial relational databases available pre-SQL, this is quite true.

Codd wrote a couple of query languages before, but they apparently weren't usable by mere mortals.

SQL is over 40 years old, and still dominates. The most recent contenders had to position themselves in terms of SQL (NoSQL).

Also interesting how even NoSQL (non relational) Data stores are embracing SQL or some subset or flavouring of it.