1

given a table 'my_table' with columns col1,col2.
Is it possible to write a query like this

SELECT col1 as my_alias,col2 FROM my_table WHERE my_alias = 'value'

I have tried it but get an unknown column 'my_alias' in where clause.

For the curious, the reason I am doing this is:

I have a table with a composite primary key. When I retrieve information from that table I concatenate the cols that make up the primary key into an Id which can then be used in my url's to identify particular records. Then when I want to return only the given record I select the record where it is = my alias. Not sure if this is a good idea, feel free to comment.

Note: The standard way to do this query is: 

SELECT col1 as my_alias,col2 FROM my_table WHERE col1 = 'value';
andrew
  • 5,096
  • 10
  • 43
  • 57

3 Answers3

5

No, it is not allowed. From the MySQL manual:

12.2.8. SELECT Syntax

...

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section C.5.5.4, “Problems with Column Aliases”.

And:

C.5.5.4. Problems with Column Aliases

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.

...

Standard SQL disallows references to column aliases in a WHERE clause.

To fix it you should write your query as follows:

SELECT col1 AS my_alias, col2
FROM my_table
WHERE col1 = 'value'

If col1 is not actually a single column but a more complicated expression then you should be aware that using it in your WHERE clause will most likely prevent efficient usage of an index and result in a full scan. This could hurt the performance of your application if the table grows large.

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Of course the method you have shown is how you should do the query. I simplified the question for the purposes of getting an answer to a specific question. I will update my question to reflect that. – andrew Feb 06 '11 at 19:18
  • I am facing the same problem with an old piece of code that worked well with MySQL 5.0. After updating to 5.5, I get the error. I have no clue how that worked, but it did. If anybody knows if there is a way of making this happen again, that would be really great. – PJP Mar 21 '12 at 09:11
2

It's not directly possible but you could use a subselect and use the column alias in the outer select:

select my_alias, col2
from (SELECT col1 as my_alias,col2 FROM my_table) as x
WHERE my_alias = 'value'
Dennis Benzinger
  • 773
  • 4
  • 6
1

You can use it in HAVING clause.

Jan
  • 2,295
  • 1
  • 17
  • 16