WHERE Clauses and Column Aliases
Problem
You want
to refer to a column alias in a WHERE
clause.
Solution
Sorry, you cannot. But there is a workaround.
Discussion
You cannot refer to column aliases in a WHERE
clause. Thus, the following statement
is illegal:
mysql>SELECT t, srcuser, dstuser, size/1024 AS kilobytes
->FROM mail WHERE kilobytes > 500;
ERROR 1054 (42S22): Unknown column 'kilobytes' in 'where clause'
The error occurs because an alias names an
output column, whereas a WHERE
clause operates on
input columns to determine which
rows to select for output. To make the statement legal, replace the
alias in the WHERE
clause with the
column or expression that the alias represents:
mysql>SELECT t, srcuser, dstuser, size/1024 AS kilobytes
->FROM mail WHERE size/1024 > 500;
+---------------------+---------+---------+-----------+ | t | srcuser | dstuser | kilobytes | +---------------------+---------+---------+-----------+ | 2006-05-14 17:03:01 | tricia | phil | 2338.3613 | | 2006-05-15 10:25:52 | gene | tricia | 975.1289 | +---------------------+---------+---------+-----------+
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.