SQL Order of Operations

SQL Order of execution vs Order of writing

SQL is a declarative language, not a procedural language. That means that the SQL compiler and optimizer determine what operations are actually run. These operations typically take the form of a directed acyclic graph (DAG) of operations.

The operators have no obvious relationship to the original query -- except that the results it generates are guaranteed to be the same. In terms of execution there are no clauses, just things like "hash join" and "filter" and "sort" -- or whatever the database implements for the DAG.

You are confusing execution with compilation and probably you just care about scoping rules.

So, to start with SQL has a set of clauses and these are in a very specified order. Your question contains this ordering -- at least for a database that supports those clauses.

The second part is the ordering for identifying identifiers. Basically, this comes down to:

Table aliases are defined in the FROM clause. So this can be considered as "first" for scoping purposes.
Column aliases are defined in the SELECT clause. By the SQL Standard, column aliases can be used in the ORDER BY. Many databases extend this to the QUALIFY (if supported), HAVING, and GROUP BY clauses. In general, databases do not support them in the WHERE clause.
If two tables in the FROM have the same column name, then the column has to be qualified to identify the table. The one exception to this is when the column is a key in a JOIN and the USING clause is used. Then the unqualified column name is fine.
If a column alias defined in the SELECT conflicts with a table alias in a clause that supports column aliases, then it is up to the database which to choose.

Order of operation for AND and OR in SQL Server queries

The page on Operator Precedence tells you:

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

And that AND has a higher precedence than OR.

However, it's not correct. In SQL, you tell the system what you want, not how to do it, and the optimizer is free to re-order operations, provided that the same logical result is produced.

So, whilst operator precedence tells you how the operators are logically combined, it does not, in fact, control the order in which each piece of logic is actually performed. This means that idioms which may be safe in other languages because of guarantees of execution order are not in fact safe in SQL. E.g. a check such as:

<String can be parsed as an int> && <convert the string to an int and compare to 20>

Can be perfectly safe in languages such as C#. The same logic in SQL is not safe since the optimizer may choose to perform the string to int conversion before it evaluates whether the string can be parsed as an int and so can throw an error about a failed conversion. (Of course, it can also work as you may have expected and not produce an error)

SQL order of operations

It depends on the database.

On SQL Server, run: SET SHOWPLAN_ALL ON then run the query, you will get an idea of what happens when it runs.

mysql OR/AND - how does order of operations work

Writing it explicitly is always a good idea, but yeah, AND comes before OR

For your pleasure, checked them all explicitly for you:

$ php -r '$d = 0; while($d < pow(2,4)){$bin = sprintf("%04b",$d); echo $bin." => ".@mysql_result(mysql_query("SELECT ".$bin[0]." AND ".$bin[1]." OR ".$bin[2]." AND ".$bin[3]),0,0).PHP_EOL;$d++;}'
0000 => 0
0001 => 0
0010 => 0
0011 => 1
0100 => 0
0101 => 0
0110 => 0
0111 => 1
1000 => 0
1001 => 0
1010 => 0
1011 => 1
1100 => 1
1101 => 1
1110 => 1
1111 => 1

So yeah, operator precedence and all. I would still throw that query back, if only it's hard for the next guy to see at a glance what it does.

Is there any specific order of execution in SQL query?

There is a logical order to evaluation of the query text, but the database engine can choose what order execute the query components based upon what is most optimal. The logical text parsing ordering is listed below. That is, for example, why you can't use an alias from SELECT clause in a WHERE clause. As far as the query parsing process is concerned, the alias doesn't exist yet.

FROM
ON
OUTER
WHERE
GROUP BY
CUBE | ROLLUP (these are not present in MySQL but are in some other SQL dialects)
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT (or, in MSSQL, TOP)

See the Microsoft documentation (see "Logical Processing Order of the SELECT statement") for more information on this.

SQL Order of Operations - GROUP BY using field created in SELECT

Your particular query has:

GROUP BY hometeam_id
---------^

This is a column in the original data, not in the SELECT. The data is aggregated at the hometeam_id level. Then the CASE expression is applied after the aggregation.

Your question supposed that the query is written using:

GROUP BY home_team

And this might or might not work, depending on the database.

SQL does not have an "order of processing". The SQL engine analyzes the query and develops a directed-acyclic graph (DAG) representing the operations that need to be performed on the data.

What you are thinking of are rules for the scoping of identifiers in SQL. The big question is where an alias defined in a SELECT can be used.

Basically no databases allow column aliases to be used in the following clauses:

SELECT
FROM
WHERE

All databases allow column aliases in the following clauses:

ORDER BY.

Some databases allow column aliases in the GROUP BY and HAVING clauses.

Your database appears to be one that allows such usage in the GROUP BY.

Do 'set operations' have an prescribed order of execution, or do they execute in order of evaluation?

All set operators have equal precedence. The documentation says

If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.