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

usar alias en where mysql

Question

La consulta que estoy ejecutando es la siguiente; sin embargo, recibo este error:

# 1054 - Columna desconocida ''guaranteed_postcode'' en ''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''
 )
)

Mi pregunta es: ¿por qué no puedo usar una columna falsa en la cláusula where de la misma consulta DB?

Answer 1

Como señaló Víctor, el problema está en 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, creo que esto es muy ineficiente, ya que la subconsulta se debe ejecutar para cada fila de la consulta externa.

Answer 2

El 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:

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

Answer 3

El estándar SQL (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 la documentación de MySQL ). Lo que puede hacer es calcular el valor de columna en la cláusula WHERE , guardar el valor en una variable y usarlo en la lista de campos. Por ejemplo, podrías 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.

Answer 4

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

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

Answer 5

Puede usar SUBSTRING ( locations . raw , -6,4) para donde condiciones

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 6

Puede usar la cláusula HAVING para filtro calculada en campos SELECT y alias

Answer 7

Quizás mi respuesta es demasiado tarde, pero esto puede ayudar a otros.

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

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

calcAlias ​​es la columna de alias que se calculó.

Answer 8

Solo puede usar alias de columna en cláusulas GROUP BY, ORDER BY o HAVING.

El SQL estándar no le permite consultar un alias de columna en una cláusula WHERE. Esta restricción se impone porque cuando se ejecuta el código WHERE, aún no se puede determinar el valor de la columna.

Copiado de la documentación de MySQL

Como se señaló en los comentarios, usar HAVING en su lugar puede hacer el trabajo. Asegúrese de dar una lectura en este DONDE vs tener aunque.