I wrote:
> sqlpro(at)sqlspot(dot)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?
regards, tom lane