Search code examples
sqlorder-of-execution

Is there any intuitive reason why SQL syntax is handled in this order?

When SQL handles a query's order of operations it begins at the 'FROM' part. Therefore, a question that can be asked is: why are queries not entered as 'FROM mytable SELECT *;' but instead as 'SELECT * FROM mytable;'

I understand that only the creators of the language have a definite answer but was looking for some intuitive explanation that I may be missing.

Solution

  • It's timely that you have asked this, because last month a group of researchers at Google published a paper describing their idea of how to "fix" the syntax for SQL.

    https://storage.googleapis.com/gweb-research2023-media/pubtools/1004848.pdf

    They note the same thing that you did, that the clauses of an SQL SELECT statement are not in the same order as they are typically executed by the RDBMS engine. They want to change SQL syntax to be more what they consider intuitive, i.e. it matches the way they think the query executes.

    enter image description here

    SQL is designed as a declarative language. You describe the result you want, and the engine is supposed to figure out the steps to get that result for you. It's meant to be an abstraction.

    It's reasonable to me that you start a query with the select-list, describing the columns you want the result to include. Then after that, you specify tables and conditions.

    E.g., "I want a list of US states and the sum of sales from each state. Get these data from the tables States and Sales. Include only sales that weren't returned."

    SELECT us_state, COUNT(*)
FROM States JOIN Sales USING (us_state)
WHERE Sales.returned = false
GROUP BY us_state;

    This is unfamiliar to some developers, who can't break their mindset that a programming language must describe the exact steps, in the exact order, to produce the result.

    They want SQL to be an imperative language instead of declarative.

    But SQL isn't a description of how the query should execute. It's a description of the result you want.