Using column alias in WHERE clause of MySQL query produces an error

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

Column Aliases can be used in GROUP BY, HAVING, or ORDER BY clauses.

In Standard SQL you won't be able to refer to a column alias in the WHERE clause. Because, whenever the WHERE clause gets executed, the column value may not be determined.

For more information, you can refer to 

https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

Enroll yourself in the best online SQL courses to learn in-depth about SQL statements, queries and become proficient in SQL.