Search code examples
sqlmariadb

MariaDB: alias in projection possible?

SELECT name AS n, CONCAT(name,'X') FROM mytab;

n     concat
------------
tim   timX
sue   sueX

When using the alias (n) in the projection it is not working.

SELECT name AS n, CONCAT(n,'X') FROM mytab;

> Unknown column 'n' in 'field list'

Is this generally not allowed or do I need to set it different to AS?

I have a longer statement and using alias in the projection would help.

Solution

  • You generally cannot refer to an alias in a SELECT clause which was defined at the same level. So this leaves you with two possible workarounds, the first of which you already know:

    SELECT name AS n, CONCAT(name, 'X') FROM mytab;

    The other option would be to alias name as n in a subquery, and then query it:

    SELECT n, CONCAT(n, 'X')
FROM
(
    SELECT name AS n
    FROM mytab
) t;