alias in where clause mysql

Question

I am writing following query in mysql, but it returning 0 rows

select * from (select col1 as mycol from tbl) temp where temp.mycol = 5

but the following query returning 4 rows

select col1 as mycol from tbl where col1 = 5

what is wrong with 1st query?

Those queries look equivalent to me. Can you reproduce this behavior on [sqlfiddle](http://sqlfiddle.com) ? — Andomar, May 30 '13 at 07:23
@Andomar - [I can't reproduce it](http://www.sqlfiddle.com/#!2/d7940/2) — Lieven Keersmaekers, May 30 '13 at 07:29
try to use `select * from (select col1 as mycol from tbl) temp where mycol = 5` remove that `temp` from `temp.mycol` — Rahul, May 30 '13 at 07:36
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. — Rahul, May 30 '13 at 07:39
@Rahul: (Earlier comment) See the link in Lieven's comment. (Later comment) It does, if the alias is defined inside a subquery and you are refering to it outside the subquery - as Mandeep is doing. — , May 30 '13 at 07:39
restart the computer and xampp has been solved the problem, thanks to all for you for help — Mandeep Singh, May 30 '13 at 07:42
i am trying with little change but it not working any suggesion??? select * from (select col1 as 'my col' from tbl) temp where 'temp.my col' = 5 — Mandeep Singh, May 30 '13 at 07:48

score 0 · Answer 1 · answered Jun 10 '13 at 23:04

In answer to your problem in your comment:

i am trying with little change but it not working any suggesion??? select * from (select col1 as 'my col' from tbl) temp where 'temp.my col' = 5 – Mandeep Singh May 30 at 7:48

The problem you are having here is that MySQL is looking for a column called 'temp.my col' but what you really want is the column called my col in the table temp So you need to do this:

select * from (select col1 as `my col` from tbl) temp where `temp`.`my col` = 5

score 0 · Answer 2 · answered Jan 20 '14 at 02:49

0

If you want to refer to an Alias (in MySQL) you need to use HAVING instead of WHERE. Your second line works because you are not referring to the alias, but the first statement fails because you are referring to an alias with WHERE.

answered Jan 20 '14 at 02:49

Griknok

384
2
13

score 0 · Answer 3 · edited May 23 '17 at 12:21

0

It works fine at my End. But I search for your problem and get this thread:

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

MySQL also states this : http://dev.mysql.com/doc/refman/5.6/en/problems-with-alias.html

Now I also confused why it works for me?

edited May 23 '17 at 12:21

Community

1
1

answered Jan 20 '14 at 06:39

Aman Aggarwal

17,619
9
53
81

alias in where clause mysql

3 Answers3