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';
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.
You can use it in HAVING clause.
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'
Look at this Can you use an alias in the WHERE clause in mysql? answer there's same statement, u need to do a query on an alias over a column operation, so if u want to use alias on condition statement u have to use HAVING so its works, other way u have to use ur concat operation on SELECT and WHERE places to work.