CODEWITHSUNDEEP
phpmysql
andrew
andrew

Reputation: 5176

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. 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';

Upvotes: 1

Views: 1888

Answers (3)

Mark Byers
Mark Byers

Reputation: 838336

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.

Upvotes: 5

Dennis Benzinger
Dennis Benzinger

Reputation: 783

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'

Upvotes: 2

Jan
Jan

Reputation: 2293

You can use it in HAVING clause.

Upvotes: 1

Related Questions