On 2021-05-30 21:44, Tom Lane wrote:
"Dean Gibson (DB Administrator)" <postgre...@mailpen.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)?
