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

## The Question :

210 people think this question is useful

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?

453 people think this answer is useful

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.

24 people think this answer is useful

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.

20 people think this answer is useful

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.

17 people think this answer is useful

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.

8 people think this answer is useful

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

1 people think this answer is useful

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



1 people think this answer is useful

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