Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-30 21:44, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql(at)mailpen(dot)com> writes:
>> I thought that having a "USING" clause, was semantically equivalent to
>> an "ON" clause with the equalities explicitly stated.  So no, I didn't
>> try that.
> USING is not that, or at least not only that ... read the manual.
>
> I'm wondering if what you saw is some side-effect of the aliasing
> that USING does.
>
> regards, tom lane

/|USING ( /|join_column|/ [, ...] )|/

/A clause of the form //|USING ( a, b, ... )|//is shorthand for
//|ON left_table.a = right_table.a AND left_table.b =
right_table.b ...|//. Also, //|USING|//implies that only one of
each pair of equivalent columns will be included in the join
output, not both./

/
/

/The //|USING|//clause is a shorthand that allows you to take
advantage of the specific situation where both sides of the join use
the same name for the joining column(s). It takes a comma-separated
list of the shared column names and forms a join condition that
includes an equality comparison for each one. For example, joining
//|T1|//and //|T2|//with //|USING (a, b)|//produces the join
condition //|ON /|T1|/.a = /|T2|/.a AND /|T1|/.b = /|T2|/.b|//./

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

/Finally, //|NATURAL|//is a shorthand form of //|USING|//: it forms
a //|USING|//list consisting of all column names that appear in both
input tables. As with //|USING|//, these columns appear only once in
the output table. If there are no common column names, //|NATURAL
JOIN|//behaves like //|JOIN ... ON TRUE|//, producing a
cross-product join./

I get that it's like NATURAL, in that only one column is included. Is
there some other side-effect?  Is the fact that I was using a LEFT JOIN,
relevant?  Is what I was doing, unusual (or risky)?