0 votes
1 view
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.4k points)

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

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 24, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...