BLACK FRIDAY EXTRA SAVINGS EVENT - EXTENDED
START FREE

Order of execution of SQL Query in 'complex queries with joins and subqueries'

My Code: Complex query with joins and subqueries.

SELECT f.name country,
       urban_pop,
       f.population total_pop,
       urban_pop/CAST(f.population AS FLOAT) urban_pct
FROM facts f  
INNER JOIN (
            SELECT facts_id, SUM(population) as urban_pop
            FROM cities c
            GROUP BY 1 ) c ON f.id=c.facts_id
WHERE urban_pct > 0.5
ORDER BY 4 

What I expected to happen:

Generally the SQL execution order will be as below.
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER
LIMIT

But in the above query i have declared an alias ‘urban_pct’ in SELECT which calculates the urban population divided by total population of the country.

Considering the order of execution of this query, the entire JOIN part after FROM will be executed first followed by WHERE followed by SELECT.
Here the condition in WHERE uses the alias ‘urban_pct’ is still not defined as it is declared in SELECT.

Ideally I should get an error something like " No field found: urban_pct" at line: 10

But the query executed fine and retrieved the results as below:

1 Like

This is expected — while the order of operations you’ve mentioned is a simplified version of how SQL works, SQL ops are much more complicated than that. SQL chooses the most optimized path to run the query successfully, and that means that it sometimes bends the “rules” to do that.

More on that here: https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/

There are many strange things in various SQL implementations that do not follow SQL standards.
Here explains your question for sqlite: https://stackoverflow.com/questions/10923107/using-a-column-alias-in-sqlite3-where-clause

Here was my question for postgres of column alias being recognized in GROUP BY: Postgres SQL GROUP BY refering to column in SELECT?

Here is a list of extra features (most with mini tutorials) for each sql flavour: https://www.sql-workbench.eu/dbms_comparison.html

1 Like

yep, I`ve got the same question.
I was about to introduce a subquery into WHERE clause when I decided to try the ‘urban_pct’ alias and, surprisingly, it worked.

According to the article provided by @dustindq:

These conditions can include references to the data and tables from the FROM clause, but cannot include any references to aliases defined in the SELECT clause, as that data and those aliases may not yet ‘exist’ in that context, as that clause wasn’t yet evaluated by the database.

Although it seems that in SQLite it works differently as explained here

This peculiarity is mentioned neither in the DQ learning materials nor in the hint, but the answer is based on it. Which is a bit confusing. I had to reread the materials from the “SQL Fundamentals” course in order to be sure that I wasn´t missing anything and no, I wasn´t missing anything.