Subject: Re: Partition by outer join
To: pgsql-sql@lists.postgresql.org
References: 
 <CAN0SRDEBQyzV2Ze0jN-rtA+SzpHSA9RP5=7hC1ASYpP-4pD5gQ@mail.gmail.com>
 <20210824063212.GA8911@depesz.com>
 <CAN0SRDGNULQKOyZ6gykyQsgtGyykbQ3XWWtBuQBRLFcXzo7-wQ@mail.gmail.com>
 <CAJexoSJrP9KTo5Xno1-q+WOpHaO3ArGBkLh5UX7S683y1X83jw@mail.gmail.com>
 <CAKFQuwaTxJsS_7pm0RjrEOZDXJS=HmVG5ZJq7Y-n=1k6Bon6Gg@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <5deed05d-fb97-9d80-853d-ca083589f5ca@gmx.net>
Date: Tue, 24 Aug 2021 18:45:17 +0200
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21)
 Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
MIME-Version: 1.0
In-Reply-To: 
 <CAKFQuwaTxJsS_7pm0RjrEOZDXJS=HmVG5ZJq7Y-n=1k6Bon6Gg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/5deed05d-fb97-9d80-853d-ca083589f5ca%40gmx.net>
Precedence: bulk


David G. Johnston schrieb am 24.08.2021 um 18:09:
>> From that stackexchange post, it appears that partition in Oracle
>> is similar to cross joins in Postgres -- where elements are
>> included in the result set as a matrix, whether they exist in the
>> source data or not (so sparsely populated values are included as
>> nulls when they exist in either of the two columns that are cross
>> joined).
>
>
> What you describe sounds like it would be an SQL Standard (I think)
> "FULL OUTER JOIN".

I think the partitioned outer join generates missing values on the fly
based on the "partition" information. So I think it's a bit different
than "just" a full outer join.