SQLite Forum

Colons in column names?
Login
The SQL-92 standard says:

   6.4  <column reference>

   <column reference> ::= [ <qualifier> <period> ] <column name>

   7.9  <query specification>

   <query specification> ::=
        SELECT [ <set quantifier> ] <select list> <table expression>

   <select list> ::=
          <asterisk>
        | <select sublist> [ { <comma> <select sublist> }... ]

   <select sublist> ::=
          <derived column>
        | <qualifier> <period> <asterisk>

   <derived column> ::= <value expression> [ <as clause> ]

   <as clause> ::= [ AS ] <column name>

   Syntax Rules

   9) Case:

      a) If the i-th <derived column> in the <select list> specifies
        an <as clause> that contains a <column name> C, then the
        <column name> of the i-th column of the result is C.

      b) If the i-th <derived column> in the <select list> does not
        specify an <as clause> and the <value expression> of that
        <derived column> is a single <column reference>, then the
        <column name> of the i-th column of the result is C.

      c) Otherwise, the <column name> of the i-th column of the <query
        specification> is implementation-dependent and different
        from the <column name> of any column, other than itself, of
        a table referenced by any <table reference> contained in the
        SQL-statement.


So when you write `SELECT tab.col FROM ...`, the output column name is
guaranteed even without an AS. But with the subquery, SQLite is forced
to invent a new name.