As I continue to learn MySQL, revisiting the fundamentals is of course, beneficial. As a matter of fact, it is integral for me to solidify many basic concepts. Nevertheless, most tutorials and blog posts only cover syntax for the chosen database.
Knowing the correct syntax is a must. But, something often overlooked is the execution order of the clauses, forming the foundation for queries. Which in turn, are very much different from their processing order. In this blog post, I will explore one of several differences. Referencing aliased column names in the WHERE
clause…
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 5.7.24

Photo by Benjamin Wagner on Unsplash
I won’t go too deep down the rabbit hole on this topic in just one blog post. My goal is to at least provide awareness, in regards to the WHERE
clause, covering a caveat that may not be that obvious until it bites you. To Note: I found great understanding, information, and inspiration for this blog post while reading the wonderful “SQL Cookbook” available on Amazon.
Starting off, let’s visit this simple SELECT
query:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SELECT * -> FROM cell_phone_2; +----+--------+--------------+ | id | f_name | cell_number | +----+--------+--------------+ | 5 | Barry | 444-111-5555 | | 1 | Josh | 222-333-4444 | | 3 | Kim | 444-111-5555 | | 2 | Mark | 888-888-0000 | +----+--------+--------------+ 4 rows in set (0.00 sec) |
For any number of reasons, you may desire to alias one (or more) of the SELECT
list columns – providing a meaningful name – using the AS
keyword. For example, I want to share my screen with a colleague and although I know what the f_name
column represents, they may not.
Therefore, I will make it crystal clear by labeling it with the aliased name, first_name
:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SELECT f_name AS first_name, cell_number -> FROM cell_phone_2; +------------+--------------+ | first_name | cell_number | +------------+--------------+ | Barry | 444-111-5555 | | Josh | 222-333-4444 | | Kim | 444-111-5555 | | Mark | 888-888-0000 | +------------+--------------+ 4 rows in set (0.00 sec) |
But, with the below query, you will come to know that syntax-wise SQL (including MySQL) is different from that of its execution and processing order. Particularly, referencing aliased columns and expressions, because of those aliased column names availability (or not). And more importantly, when they are available.
Here is one such example of the divergence between syntax and the WHERE
clause execution:
1 2 3 4 | mysql> SELECT id AS info, f_name, cell_number -> FROM cell_phone_2 -> WHERE info = 5; ERROR 1054 (42S22): Unknown column 'info' in 'where clause' |
What is going on here?
The above error is reported because the SELECT
clause has not been processed yet. In other words, it has not provided the listed column items.
What is worse, the WHERE
clause executes its boolean conditional check on an ‘info’ column, that does not exist (yet).
“What does that mean?”
This can be downright confusing. After all, SELECT
is the first clause, syntax-wise, in the query.
To better understand why and how this occurs, let’s look at the query with no aliases and visit its (generalized) execution order in turn.
1 2 3 4 5 6 7 8 9 | mysql> SELECT id, f_name, cell_number -> FROM cell_phone_2 -> WHERE id = 5; +----+--------+--------------+ | id | f_name | cell_number | +----+--------+--------------+ | 5 | Barry | 444-111-5555 | +----+--------+--------------+ 1 row in set (0.00 sec) |
- First, the
FROM
clause loads all rows from tablecell_phone_2
for processing. It is super important to realize this step happens first in the query. - Next, the
WHERE
clause filters out any of those row(s), provided by theFROM
clause, that does not have anid
column value of 5. - Finally the
SELECT
clause projects (for display) the columns:id
,f_name
, andcell_number
from the available processed rows. Remember, since this query has aWHERE
clause,SELECT
can only provide the listed columns from the remaining rows (if any). This is yet another critical concept to understand. I must also make this additional, important note here; the above-discussed execution order is specific for this particular query. Had aGROUP BY
orHAVING
clause been present,SELECT
would execute only after both of those clauses had completed processing as well.
You can begin to see what is going on with the example query, using the aliased column. But, let’s break it (the failing query) down into more manageable chunks, and know exactly where the hang up is:
- The
FROM
clause loads all rows from tablecell_phone_2
for processing. - The
WHERE
clause attempts to filter out any rows that do not have an'info'
column value of 5. Here is the problem. There is no column'info'
in tablecell_phone_2
. Because any column aliases are not available until theWHERE
clause has finished processing rows from the data source(s). Then,SELECT
can provide the columns for projection.
Well, that stinks.
I guess you just cannot have these types of queries then huh?
Nonsense!
Of course, you can!
One of the many beauties of SQL is that there is almost always more than one way to obtain needed results.
Chances are, workarounds exist for a number of issues you may encounter, and that applies to this scenario as well. To circumvent this aliased column problem, you can use a Derived Table.
I’ll go ahead and display the working query. Then, we will visit the query processing order to understand why it works.
1 2 3 4 5 6 7 8 9 10 | mysql> SELECT * -> FROM (SELECT id AS info, f_name, cell_number -> FROM cell_phone_2) AS tb_1 -> WHERE info = 5; +------+--------+--------------+ | info | f_name | cell_number | +------+--------+--------------+ | 5 | Barry | 444-111-5555 | +------+--------+--------------+ 1 row in set (0.01 sec) |
Again, I visit the query execution step by step:
- We have said previously that the
FROM
clause is the first executed, providing all the available rows from the table (or data source(s) as we will come to see) named in its clause. This is where the Derived Table is formed in this example query. The innerSELECT
provides a complete results set for theFROM
clause to process, containing this data:1
2
3
4
5
6
7
8
9+------+--------+--------------+
| info | f_name | cell_number |
+------+--------+--------------+
| 5 | Barry | 444-111-5555 |
| 1 | Josh | 222-333-4444 |
| 3 | Kim | 444-111-5555 |
| 2 | Mark | 888-888-0000 |
+------+--------+--------------+
4 rows in set (0.00 sec)Notice what else it provides?
An aliased column namedinfo
. - Next, the
WHERE
clause filters out any of the provided result set rows that do not have aninfo
column value of 5. You are likely wondering why now, all of a sudden, this aliased column name works? Since theFROM
clause has finished processing and providing all the available rows, the aliasedinfo
column is now visible to the outerWHERE
clause by way of the Derived Table. Remember,FROM
processes first. - Finally,
SELECT *
– shorthand notation for ‘all columns’ – is processed, projecting the final query results.
Another important point to note when working with Derived Tables in MySQL is they must be aliased.
According to the official documentation in section, 13.2.10.8 Derived Tables, the reasoning is that all tables in the FROM
clause must be named.
Below, I have removed the Derived Table alias tb_1
, from the example query used above:
1 2 3 4 5 | mysql> SELECT * -> FROM (SELECT id AS info, f_name, cell_number -> FROM cell_phone_2) -> WHERE info = 5; ERROR 1248 (42000): Every derived table must have its own alias |
The reported error itself is self-explanatory.
SQL clauses can be tricky to understand at times since their execution order does not follow that of the syntax. Time spent digesting this concept is well invested and can alleviate much confusion moving forward.
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
Explore the official MySQL 5.7 Online Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
I was having a tough time with why my query was saying the attribute doesn’t exist. This was the perfect post for me. Thanks!
Hi Adil. Thanks so much for the compliment. I’m very glad the post helped you with your query. Thanks