Utilizzando l’alias di colonna nella clausola WHERE della query MySQL si produce un errore

La query che sto utilizzando è la seguente, tuttavia sto ricevendo questo errore:

# 1054 – Colonna sconosciuta ‘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' ) ) 

La mia domanda è: perché non riesco a utilizzare una colonna falsa nella clausola where della stessa query DB?

È ansible utilizzare solo alias di colonna nelle clausole GROUP BY, ORDER BY o HAVING.

SQL standard non consente di fare riferimento a un alias di colonna in una clausola WHERE. Questa restrizione è imposta perché quando viene eseguito il codice WHERE, il valore della colonna potrebbe non essere ancora determinato.

Copiato dalla documentazione MySQL

Come indicato nei commenti, l’uso di HAVING potrebbe invece funzionare. Assicurati di dare una lettura a questo WHERE vs HAVING però.

Come ha sottolineato Victor, il problema è con l’alias. Questo può essere evitato, ponendo l’espressione direttamente nella clausola WHERE x IN:

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

Tuttavia, immagino che questo sia molto inefficiente, dal momento che la sottoquery deve essere eseguita per ogni riga della query esterna.

SQL standard (o MySQL) non consente l’utilizzo di alias di colonna in una clausola WHERE perché

quando viene valutata la clausola WHERE, il valore della colonna potrebbe non essere stato ancora determinato.

(dalla documentazione di MySQL ). Quello che puoi fare è calcolare il valore della colonna nella clausola WHERE , salvare il valore in una variabile e usarlo nell’elenco dei campi. Ad esempio potresti fare questo:

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

Ciò evita di ripetere l’espressione quando diventa complicata, rendendo il codice più facile da mantenere.

Forse la mia risposta è troppo tardi, ma questo può aiutare gli altri.

Puoi racchiuderlo con un’altra istruzione select e usare la clausola where.

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

calcAlias ​​è la colonna alias che è stata calcasting.

È ansible utilizzare la clausola HAVING per il filtro calcasting nei campi SELECT e alias

Sto usando mysql 5.5.24 e il seguente codice funziona:

 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 standard non consente i riferimenti agli alias di colonna in una clausola WHERE. Questa restrizione è imposta perché quando viene valutata la clausola WHERE, il valore della colonna potrebbe non essere stato ancora determinato. Ad esempio, la seguente query è illegale:

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

È ansible utilizzare SUBSTRING ( locations raw , -6,4) per dove 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' ) )