Thread: [GENERAL] Fields re-ordered on JOIN with * and USING

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Sep 1, 2017 at 2:25 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>> Hi.  I recently noticed that when doing a SELECT * with USING, that the
>> join field(s) appear first in the output.  I'd never noticed that before,
>> and was just curious if that is expected behavior or not.  Thanks.

> ​I don't recall if or where it is documented but it is intentional., as is
> the documented fact that only one instance of the named column appears in
> the output.

Yes, it's documented, here:

https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-FROM

under "7.2.1.1. Joined Tables":

    Furthermore, the output of JOIN USING suppresses redundant columns:
    there is no need to print both of the matched columns, since they must
    have equal values. While JOIN ON produces all columns from T1 followed
    by all columns from T2, JOIN USING produces one output column for each
    of the listed column pairs (in the listed order), followed by any
    remaining columns from T1, followed by any remaining columns from T2.

            regards, tom lane

... btw, a little digging shows that this ordering is required by the
SQL standard.  The least excruciating version of the relevant text is
in SQL92 7.5 <joined table>:

            d) Let SLCC be a <select list> of <derived column>s of the form

                 COALESCE ( TA.C, TB.C ) AS C

              for every column C that is a corresponding join column, taken
              in order of their ordinal positions in T1.

            e) Let SL1 be a <select list> of those <column name>s of T1
              that are not corresponding join columns, taken in order of
              their ordinal positions in T1, and let SLT2 be a <select
              list> of those <column name>s of T2 that are not correspond-
              ing join columns, taken in order of their ordinal positions
              in T2.

            f) The descriptors of the columns of the result of the <joined
              table> are the same as the descriptors of the columns of the
              result of

                 SELECT SLCC, SLT1, SLT2 FROM TR1, TR2


Later versions of the standard use many more words to say the same thing.

            regards, tom lane