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