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