282

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?

Improve this question
0

7 Answers 7

Reset to default
545

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Cheers for the quick and accurate response! I've had a look into the HAVING clause and worked out a way to successfully run this query. Thanks again.
In case anyone else has same prob as me which was using the aliased col in a where clause failing - swapping the 'WHERE' for 'HAVING fixed it straight away +1 good answer.
This may or may not be important in your case, but HAVING executes slower than WHERE
The reason having works is because the column values have to be computed by the time you get to the having. This isn't the case with where, as stated above.
@MillieSmith, That's true only for aggregate predicates in having. As we know, having can have non-aggregate predicates, just like where. In such a case, it isn't hard for the optimizer to convert that having predicate to an expanded where predicate. As such, citation needed. You can find it here: stackoverflow.com/a/18710763/632951
|
30

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.

Improve this answer

Comments

24

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.

Improve this answer

1 Comment

@rodion, Yes I believe this is severely slow and inefficient.
23

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.

Improve this answer

7 Comments

Doesn't this conflict with the documentation that says "As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed."?
That's definitely something to keep in mind. It has always worked for me though, I think the order of evaluation of the different parts of a statement had to be fixed (first WHERE, then SELECT, then GROUP BY,...) but I don't have a reference for that
A few examples: some claim that for them 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.
@Joni, Why not just evaluate the condition twice? Surely MySQL is smart enough to optimize that.......
@Pacerier having to repeat the expression is still worse especially if it's complicated. I have not been able to confirm if MySQL implements common subexpression elimination.
|
8

You can use HAVING clause for filter calculated in SELECT fields and aliases

Improve this answer

2 Comments

@fahimg23 - Not sure. I tried to find a reason why, but I can't! Keep in mind the differences between WHERE and HAVING, though. They're not identical. stackoverflow.com/search?q=where+vs+having
UPDATE: It's because this answer provides the same solution but with more info.
2

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' ) )
Improve this answer

Comments

2

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' ) )
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.