Understanding WHERE clause execution in MySQL with Derived Tables.

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 WHEREclause…

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:

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:

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:

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. First, the FROM clause loads all rows from table cell_phone_2 for processing. It is super important to realize this step happens first in the query.
2. Next, the WHERE clause filters out any of those row(s), provided by the FROM clause, that does not have an id column value of 5.
3. Finally the SELECT clause projects (for display) the columns: id, f_name, and cell_number from the available processed rows. Remember, since this query has a WHERE 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 a GROUP BY or HAVING 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:

1. The FROM clause loads all rows from table cell_phone_2 for processing.
2. 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 table cell_phone_2. Because any column aliases are not available until the WHERE 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.

Again, I visit the query execution step by step:

1. 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 inner SELECT provides a complete results set for the FROM 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 named info.

2. Next, the WHERE clause filters out any of the provided result set rows that do not have an info column value of 5. You are likely wondering why now, all of a sudden, this aliased column name works? Since the FROM clause has finished processing and providing all the available rows, the aliased info column is now visible to the outer WHERE clause by way of the Derived Table. Remember, FROM processes first.
3. 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:

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.