mysql别名错误,在MySQL查询的WHERE子句中使用列别名会产生错误

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?

解决方案

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL doesn't allow you to

refer to a column alias in a WHERE

clause. This restriction is imposed

because when the WHERE code is

executed, the column value may not yet

be determined.

As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this WHERE vs HAVING though.