SQL clauses - order of execution

Order of execution says SELECT comes after WHERE clause but how is the WHERE clause accepting the aliases that get defined in SELECT clause? Query copied and pasted from one of the missions:

SELECT Major AS m, Major_category AS mc, Unemployment_rate AS ur
FROM recent_grads
WHERE (mc = ‘Engineering’) AND (ur > 0.04 and ur < 0.08)
ORDER BY ur DESC

1 Like

Hi @manil.pod:

I think this article answers your question. To quote it:

WHERE
Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded. Each of the constraints can only access columns directly from the tables requested in the FROM clause. Aliases in the SELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.

SELECT
Any expressions in the SELECT part of the query are finally computed.

Hope this clarifies your doubt.

Thanks for the prompt response.

It’s helpful. By the way, I tried it in the T-SQL, and the alias was not accessible to WHERE clause. Not sure if it’s a good practice and may not be a good idea to put that example query in the mission… just my opinion.

Thank you!

1 Like

Hmm yes @manil.pod. Usually we do aliases mainly so that we don’t have to type out the entire table name when doing a inner join, not so much for individual columns from a single table.

Could you provide a link to the mission you are referring to? You could fill out this ticket to give your feedback to the content creation team.

https://stackoverflow.com/questions/10923107/using-a-column-alias-in-sqlite3-where-clause#:~:text=Standard%20SQL%20doesn’t%20allow,may%20not%20yet%20be%20determined.&text=SQL%20standard%2C%20column%20aliases%20can,GROUP%20BY%20and%20HAVING%20clauses.

Sqlite allows column alias in ORDER BY, GROUP BY, HAVING, WHERE and JOIN ON.


Postgres has different alias behavior too.

To be a comprehensive learner, know what is part of the sql standard, meaning which way of writing the query makes it easiest to copy paste across sql engines without much edits needed, then learn extensions of each flavour to write using shortcuts.

If you think this is tedious, wait til you see null handling across db: https://www.sqlite.org/nulls.html

Thank you Ryan! I have some experience using T-SQL and this is the first time I’m using SQLite. It’s interesting to see some differences.

Anyway, the query I mentioned is in Summary Statistics >> 7. Customizing the Results

SELECT Major AS m, Major_category AS mc, Unemployment_rate AS ur
FROM recent_grads
WHERE (mc = ‘Engineering’) AND (ur > 0.04 and ur < 0.08)
ORDER BY ur DESC

Sure. Do you mind marking my reply as the solution if you found it helpful? Thanks!

Sure thing. Done!

Thank you

1 Like