Using an alias within a join

Question

I'm trying to use an SQL alias within a left join. While using the alias in the join, I get the error 'unknown a_alias column in on clause' Could anyone point me in the right direction?

SELECT a as a_alias FROM table_a LEFT JOIN table_b ON a_alias = b

Cheers

I'm ever so sorry, I accidentally typed 'where' instead of 'on' — user2708932
– user2708932, Commented Jun 11, 2012 at 14:39

Jeff Watkins · Accepted Answer · 2012-06-11 14:41:13Z

1

You can't use the alias in a where or a join as it hasn't been evaluated yet. They're only available in aggregates and group by/order by/having clauses IIRC.

answered Jun 11, 2012 at 14:41

Jeff Watkins

6,35718 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cdhowie · Accepted Answer · 2012-06-11 14:41:28Z

0

The only aliases you can use in ON are any table aliases; the SELECT clause is first in the grammar, but is (usually) the last processing step; the projection you ask for in the SELECT clause is not available yet. You cannot use column aliases defined in the SELECT clause in an ON clause -- you need to specify the full column name.

answered Jun 11, 2012 at 14:41

cdhowie

172k25 gold badges303 silver badges324 bronze badges

3 Comments

user2708932 Over a year ago

Ah, I see. I shortened my query by a lot, the actual code I use is SELECT AES_DECRYPT(column_a, 'key') as a_alias FROM table_a ... That's why I wanted to use an alias

user2708932 Over a year ago

Ended up just selecting the column I needed and decrypting it in the on clause. My problem is solved, thank you! :)

cdhowie Over a year ago

No problem. Note that if you wanted to save the computation time (assuming that MySQL can't optimize away the second call) you could always execute the function in a subquery (SELECT a_alias FROM (SELECT a AS a_alias FROM table_a) AS a_query LEFT JOIN table_b ON a_query.a_alias = table_b.b).

Taryn · Accepted Answer · 2012-06-11 14:49:53Z

0

If you want to use an alias in a query you can do the following:

SELECT *
FROM 
(
    SELECT a as a_alias
    FROM table_a
) ta
LEFT JOIN table_b 
    ON ta.a_alias = b

You can wrap your SELECT ... FROM in parentheses and then use the table alias and the column alias in your JOIN.

answered Jun 11, 2012 at 14:49

Taryn

249k57 gold badges374 silver badges409 bronze badges

Collectives™ on Stack Overflow

Using an alias within a join

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related