El uso de alias de columna en la cláusula WHERE de la consulta MySQL produce un error

Como Victor señaló, el problema es con el alias. Sin embargo, esto se puede evitar poniendo la expresión directamente en la cláusula WHERE x IN y:

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

Sin embargo, supongo que esto es muy ineficiente, ya que la subconsulta tiene que ejecutarse para cada fila de la consulta externa.

SQL estándar (o MySQL) no permite el uso de alias de columna en una cláusula WHERE porque

Cuando se evalúa la cláusula WHERE, es posible que el valor de la columna aún no se haya determinado.

(de documentación MySQL ). Lo que puede hacer es calcular el valor de la columna en la cláusula WHERE, guardar el valor en una variable y usarlo en la lista de campos. Por ejemplo, usted podría hacer esto:

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

Esto evita repetir la expresión cuando se complica, haciendo que el código sea más fácil de mantener.

Tal vez mi respuesta es demasiado tarde, pero esto puede ayudar a otros.

Puede encerrarlo con otra instrucción select y usar la cláusula where.

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

CalcAlias es la columna alias que se calculó.

Puede usar la cláusula HAVING para el filtro calculado en SELECCIONAR campos y alias

Estoy usando mysql 5.5.24 y el siguiente código funciona:

select * from (
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`
) as a
WHERE guaranteed_postcode NOT IN --this is where the fake col is being used
(
 SELECT `postcode` FROM `postcodes` WHERE `region` IN
 (
  'australia'
 )
)

SQL estándar no permite referencias a alias de columna en una cláusula WHERE. Esta restricción se impone porque cuando se evalúa la cláusula WHERE, es posible que el valor de la columna aún no se haya determinado. Por ejemplo, la siguiente consulta es ilegal:

SELECCIONE id, CUENTE(*) COMO cnt DESDE tbl_name DONDE cnt > 0 AGRUPA POR id;

Puede usar SUBCADENA(locations.raw, -6,4) para donde 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'
)
)