Mysql Where Alias equals

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. Th开发者_如何学JAVAen 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';

---

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.

---

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'

---

You can use it in HAVING clause.