Using column alias in WHERE clause of MySQL query produces an error
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?
You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.
Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.
Copied from MySQL documentation
As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this question too: WHERE vs HAVING.
As Victor pointed out, the problem is with the alias. This can be avoided though, by putting the expression directly into the WHERE x IN y clause:
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'
)
)
However, I guess this is very inefficient, since the subquery has to be executed for every row of the outer query.
Standard SQL (or MySQL) does not permit the use of column aliases in a WHERE clause because
when the WHERE clause is evaluated, the column value may not yet have been determined.
(from MySQL documentation). What you can do is calculate the column value in the WHERE clause, save the value in a variable, and use it in the field list. For example you could do this:
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'
)
)
This avoids repeating the expression when it grows complicated, making the code easier to maintain.
select @code:=sum(2), 2*@code works in MySQL 5.5, but for me in 5.6 the second column yields NULL on first invocation, and returns 2 times the previous result when run again. Interesting enough, both select @code:=2, 2*@code and select @code:=rand(), 2*@code do seem to work in my 5.6 (today). But those are indeed writing and reading in the SELECT clause; in your case you're setting it in WHERE.
Maybe my answer is too late but this can help others.
You can enclose it with another select statement and use where clause to it.
SELECT * FROM (Select col1, col2,...) as t WHERE t.calcAlias > 0
calcAlias is the alias column that was calculated.
You can use HAVING clause for filter calculated in SELECT fields and aliases
WHERE and HAVING, though. They're not identical. stackoverflow.com/search?q=where+vs+having
I am using mysql 5.5.24 and the following code works:
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'
)
)
You can use SUBSTRING(locations.raw,-6,4) for where 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'
)
)
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- SQL JOIN - WHERE clause vs. ON clause
- How can I output MySQL query results in CSV format?
- How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
- INNER JOIN ON vs WHERE clause
- How can I get a list of user accounts using the command line in MySQL?
- MySQL error code: 1175 during UPDATE in MySQL Workbench
- How do I import an SQL file using the command line in MySQL?
HAVINGexecutes slower thanWHEREhavingworks is because the column values have to be computed by the time you get to thehaving. This isn't the case withwhere, as stated above.