Re: BUG #9896: Bug in FULL OUTER JOIN

On 04/07/2014 10:17 PM, Tom Lane wrote:
> I wrote:
>> sqlpro@sqlspot.com writes:
>>> SELECT *
>>> FROM   T_CLIENT_CLI AS C
>>> FULL OUTER JOIN T_PROSPECT_PSP AS P
>>> ON C.CLI_SIREN = P.PSP_SIREN
>>> OR C.CLI_ENSEIGNE = P.PSP_ENSEIGNE;
>> Can you show us the query plans used by those systems?
> For the archives' sake: the OP sent me a not-too-useful screen shot
> in which SQL Server claims it's using a merge join for this query.
>
> I find this less than credible: what linear sort order would bring
> together all the potentially joinable rows?  If they're getting the
> right answer at all, there must be some secret sauce in there someplace.
> Perhaps they're just Doing It The Hard Way with state storage
> proportional to the size of the relations?

And Oracle does it in two steps: http://www.sqlfiddle.com/#!4/607e0/1/0

--
Vik