Using column alias in WHERE clause of MySQL query

Question

The query I'm running is as follows, however I'm getting this error:

#1054 - Unknown column 'guaranteed_postcode' in 'IN/ALL/ANY subquery'

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 question is: why am I unable to use a fake column in the where clause of the same DB query?

Answer 1

Standard SQL (or MySQL) does not permit the use of column aliases in a WHERE clause because

when the WHERE clause is evaluated, the column value may not yet have been determined.

(from MySQL documentation). What you can do is calculate the column value in the WHERE clause, save the value in a variable, and use it in the field list. For example you could do this:

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 avoids repeating the expression when it grows complicated, making the code easier to maintain.

Answer 2

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

Answer 3

You can use SUBSTRING(locations.raw,-6,4) for where conditon

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'
)
)

Answer 4

As Victor pointed out, the problem is with the alias. This can be avoided though, by putting the expression directly 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 guess this is very inefficient, since the subquery has to be executed for every row of the outer query.

Answer 5

Maybe my answer is too late but this can help others.

You can enclose it with another select statement and use where clause to it.

SELECT * FROM (Select col1, col2,...) as t WHERE t.calcAlias > 0

calcAlias is the alias column that was calculated.

Answer 6

You can use HAVING clause for filter calculated in SELECT fields and aliases