Using column alias in WHERE clause of MySQL query produces an error

Question

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?

victor hugo · Accepted Answer · 2020-11-20T11:20:32.917

531

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.

edited Nov 20 '20 at 11:20

answered Jun 03 '09 at 00:38

victor hugo

35,514
12
68
79

1

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. – James Jun 03 '09 at 00:42
57

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. – megaSteve4 May 28 '12 at 00:14
@megaSteve4 I did have the same problem! Using "HAVING" solved it smoothly. :) – Johan May 02 '13 at 23:02
14

This may or may not be important in your case, but `HAVING` executes slower than `WHERE` – DTs Apr 25 '14 at 08:38
@megaSteve4, What, simply that and it works? Then why couldn't MySQL be smart enough to do that automatically for us? – Pacerier Apr 09 '15 at 21:29
@DTs ultimately yes - it obviously depends on your query though. More here - http://stackoverflow.com/questions/16155937/mysql-having-vs-where – megaSteve4 Apr 29 '15 at 16:35
@DTs, Citation needed. – Pacerier May 06 '15 at 15:32
4

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. – Millie Smith May 08 '15 at 20:01
1

@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: http://stackoverflow.com/a/18710763/632951 – Pacerier May 10 '15 at 08:59
@Pacerier The fact is that in this case MySQL is smarter, because is letting you choose if you want this condition to be checked before or after you get the fieldset. This is important in most of the queries. With "group by" for example the result is absolutely different. – José Manuel Blasco Feb 21 '19 at 08:51
@MillieSmith how do you evaluate where conditions without values? For me, the statement in the post makes no sense, because to check a condition (e.g. `a < 5`) you need the actual column value (e.g. value of `a` to check `a < 5`). – Sebi2020 Sep 20 '21 at 15:37
1

Note after [December 2009](https://web.archive.org/web/20091215124608/http://dev.mysql.com:80/doc/refman/5.1/en/problems-with-alias.html) the MySQL documentation got rephrased into "Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined." – Jeroen Wiert Pluimers Dec 01 '22 at 11:58
before you leave assuming there also is no alternate available as well, do read below answered to this Q: https://stackoverflow.com/a/25568879/10249156 Thanks – Wasit Shafi Mar 08 '23 at 20:05

score 26 · Answer 2 · answered Aug 29 '14 at 13:03

26

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.

answered Aug 29 '14 at 13:03

George Khouri

304
3
7

This alternate way works with MySQL. Thanks @george-khouri – Wasit Shafi Mar 08 '23 at 19:27

score 24 · Answer 3 · answered Jun 03 '09 at 00:47

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.

@rodion, Yes I believe this is **severely** slow and inefficient. — Pacerier, Apr 09 '15 at 21:31

Joni · Answer 4 · 2017-07-20T05:30:27.663

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.

edited Jul 20 '17 at 05:30

answered Jul 27 '12 at 10:14

Joni

108,737
14
143
193

13

Doesn't this conflict with the documentation [that says](http://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and-use-it-within-the-same-select/16715618#16715618) *"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."*? – Arjan Nov 19 '14 at 08:58
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 – Joni Nov 21 '14 at 06:44
A few examples: some [claim](http://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and-use-it-within-the-same-select/24551337#24551337) 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. – Arjan Nov 21 '14 at 07:59
@Joni, Why not just evaluate the condition twice? Surely MySQL is smart enough to optimize that....... – Pacerier Apr 09 '15 at 21:37
@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. – Joni Apr 12 '15 at 18:22
@Joni, If it doesn't it's simply dumb. It should since we didn't use any non-deterministic functions like `rand()` `now()` etc. – Pacerier Apr 13 '15 at 13:36
`WHERE (@postcode := SUBSTRING(`locations`.`raw`,-6,4)) NOT IN` This will be inefficient as it would run for each of the record in outer query. – Shiv May 28 '19 at 07:41

Hett · Answer 5 · 2016-02-15T07:35:12.993

8

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

edited Feb 15 '16 at 07:35

answered Apr 14 '15 at 09:40

Hett

3,484
2
34
51

@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. https://stackoverflow.com/search?q=where+vs+having – rinogo Jun 22 '17 at 21:05
UPDATE: It's because [this answer](https://stackoverflow.com/a/942592/114558) provides the same solution but with more info. – rinogo Jun 22 '17 at 21:07

score 2 · Answer 6 · edited Nov 09 '16 at 03:13

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

score 2 · Answer 7 · answered Mar 21 '18 at 10:02

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

Using column alias in WHERE clause of MySQL query produces an error

7 Answers7

Linked

Related