Wednesday, February 17, 2010

Column aliasing in SQL

Consider the following example:


CREATE TABLE t (c1 int);

SELECT * FROM t AS a(a1);

SELECT c1 AS a1 FROM t AS a;


In both cases, the result of the SELECT is a table named "A" with a column named "A1".

However, the syntax for specifying the column alias differs in the two cases.

And, some other similar syntax does not seem to be legal (at least in Derby):


SELECT c1 FROM t AS a(a1);

ERROR 42X04: Column 'C1' is either not in any table in
the FROM list or appears within a join specification and
is outside the scope of the join specification or appears
in a HAVING clause and is not in the GROUP BY list. If
this is a CREATE or ALTER TABLE statement then 'C1'
is not a column in the target table.

SELECT c1 AS a1 FROM t AS a(a2);

ERROR 42X04: Column 'C1' is either not in any table in
the FROM list or appears within a join specification and
is outside the scope of the join specification or appears
in a HAVING clause and is not in the GROUP BY list. If
this is a CREATE or ALTER TABLE statement then 'C1'
is not a column in the target table.


I'm not really sure what's going on with this part of the SQL language.

Normally, I have always used the form

SELECT c1 AS a1 FROM t AS a;


I only learned about the FROM t AS a(a1) form fairly recently, and I'm still trying to understand what it means and how it is to be used:

  • Can I only use it with SELECT *

  • How do I know what order the columns are to be named in

  • Why would I choose to use this form as opposed to the individual column aliasing?



So much still left to learn about SQL...

1 comment:

  1. did you solve this problem, i am having the same issue pls post your answer

    ReplyDelete