Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lnaPM-0002F4-ST for pgsql-performance@arkaria.postgresql.org; Mon, 31 May 2021 05:24:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lnaPK-0008AB-Pr for pgsql-performance@arkaria.postgresql.org; Mon, 31 May 2021 05:24:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lnaPK-0008A1-Gc for pgsql-performance@lists.postgresql.org; Mon, 31 May 2021 05:24:10 +0000 Received: from aws.mailpen.com ([52.43.43.172] helo=aws2.mailpen.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lnaPC-0004vB-Cd for pgsql-performance@postgresql.org; Mon, 31 May 2021 05:24:08 +0000 Received: from [172.20.70.208] (unknown [50.35.114.95]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by aws2.mailpen.com (Postfix) with ESMTPSA id 22614C7E7D9 for ; Sun, 30 May 2021 22:24:00 -0700 (PDT) Subject: Re: AWS forcing PG upgrade from v9.6 a disaster To: pgsql-performance@postgresql.org References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com> <92cc8563-195f-8727-d887-5f2bb63f231f@mailpen.com> <2370D2C6-4D91-4C96-9410-11EFD9954B21@thebuild.com> <5463af86-9007-1094-78bf-18e54c60edaf@mailpen.com> <2524029.1622436267@sss.pgh.pa.us> From: "Dean Gibson (DB Administrator)" Organization: UltiMeth Systems Message-ID: <6e47da10-5eb2-c54c-c77e-f27ba04e094b@mailpen.com> Date: Sun, 30 May 2021 22:24:00 -0700 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101 Thunderbird/78.10.2 MIME-Version: 1.0 In-Reply-To: <2524029.1622436267@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------DF2B8B3608E97EDD7462E2BF" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------DF2B8B3608E97EDD7462E2BF Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 2021-05-30 21:44, Tom Lane wrote: > "Dean Gibson (DB Administrator)" 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)? --------------DF2B8B3608E97EDD7462E2BF Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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)?



--------------DF2B8B3608E97EDD7462E2BF--