Back

Explore Courses Blog Tutorials Interview Questions
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

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

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)
0 votes
1 answer
asked Jul 12, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...