SQL query causes "Unknown Column In Where Clause" error due to alias

question:

Query using alias in a SELECT statement will cause an error, prompting that the unknown alias column in the WHERE statement. For example, the following query triggers this error:

SELECT u_name AS user_name FROM users WHERE user_name = "john";

explain:

SQL execution order is from right to left. In this example, the WHERE clause is executed before the SELECT clause. Therefore, when parsing the WHERE clause, the alias user_name is not yet defined.

Solution:

To solve this problem, you can use the original column name in the WHERE clause:

SELECT u_name AS user_name FROM users WHERE u_name = "john";

Or enclose the alias column names in brackets in the WHERE clause:

SELECT u_name AS user_name FROM users WHERE (user_name = "john");