Problem
I’m running the following query, but I’m receiving the following error:
SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,
SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE `guaranteed_postcode` NOT IN #this is where the fake col is being used
(
SELECT `postcode` FROM `postcodes` WHERE `region` IN
(
'australia'
)
)
My issue is why can’t I utilize a fake column in the same DB query’s where clause?
Asked by James
Solution #1
Column aliases can only be used in GROUP BY, ORDER BY, or HAVING clauses.
This was taken from the MySQL manual.
Using HAVING instead, as suggested in the comments, may suffice. Take a look at this question as well: WHERE vs. HAVING.
Answered by victor hugo
Solution #2
The issue, as Victor pointed out, is with the alias. However, this can be avoided by directly inserting the expression into the WHERE x IN y clause:
SELECT `users`.`first_name`,`users`.`last_name`,`users`.`email`,SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE SUBSTRING(`locations`.`raw`,-6,4) NOT IN #this is where the fake col is being used
(
SELECT `postcode` FROM `postcodes` WHERE `region` IN
(
'australia'
)
)
However, I believe that approach is wasteful because the subquery must be run for each row of the outer query.
Answered by rodion
Solution #3
Because of this, column aliases cannot be used in a WHERE clause in standard SQL (or MySQL).
(according to MySQL docs) You can use the WHERE clause to calculate the column value, save it in a variable, and utilize it in the field list. You could, for example, do the following:
SELECT `users`.`first_name`, `users`.`last_name`, `users`.`email`,
@postcode AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE (@postcode := SUBSTRING(`locations`.`raw`,-6,4)) NOT IN
(
SELECT `postcode` FROM `postcodes` WHERE `region` IN
(
'australia'
)
)
This eliminates the need to repeat the expression as it becomes more complex, making the code easier to maintain.
Answered by Joni
Solution #4
Perhaps my response is too late, but it may be useful to others.
You can use a where clause to surround it in another select statement.
SELECT * FROM (Select col1, col2,...) as t WHERE t.calcAlias > 0
The calculated alias column is named calcAlias.
Answered by George Khouri
Solution #5
For filters calculated in SELECT fields and aliases, you can use the HAVING clause.
Answered by Hett
Post is based on https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error