Den 23.01.2017 13:05, skrev Kjell Rilbe kjell.rilbe@... [firebird-support]:

Hi,

I just needed to do a full outer join between two very simple tables G and N, each with a single-column PK of identical type char(12). I noticed that the plan produces was:

PLAN JOIN (N NATURAL, G NATURAL)

I stopped the query after ~5 minutes. Then I thought I can do this calculation in two steps, so I did a left outer join from G to N first, then a left outer join from N to G (equivalent to a right outer join from G to N). These queries were executed with these plans:

PLAN JOIN (G NATURAL, N INDEX (PK_N))
PLAN JOIN (N NATURAL, G INDEX (PK_G))

They both executed in less than half a minute each.

Not quite answering your question, Kjell, but I do remember having observed that some queries with outer joins take too long and that I concluded that Firebird is great with inner JOINs, but performancewise rather poor with some outer JOINs. I think I once solved one such case using a CTE (at least I attempted using a CTE. I think, but am not 100% certain it worked. However, my query involved more than two tables, as indicated by M in square brackets below):

WITH TMP(PK) as
(SELECT PK FROM G
 UNION
 SELECT PK FROM N)
SELECT ...
FROM TMP
LEFT JOIN G ON TMP.PK = G.PK
LEFT JOIN N ON TMP.PK = N.PK
[ JOIN M ON M.Field = COALESCE(G.Field, N.Field) ]

Good to read you found a similar way to solve your particular problem.
Set

Nice one! I haven't really learned to use CTEs yet, so this was a good kick in the <whatever> to do so. :-)

Kjell