> I need to use the LEFT JOIN in SQL and I find that the performance is really bad.
> For some reason, the right part of the SQL is evaluated for each left record.
>
> For example, I have converted my consult in a very basic SQL to check it. I use nested
> tables, here it is trivial, but in my app, I need to do it for several reasons:
>
> SELECT * FROM
> (SELECT 1 AS VAL FROM rdb$database
> UNION SELECT 2 FROM rdb$database
> UNION SELECT 3 FROM rdb$database
> UNION SELECT 4 FROM rdb$database) T1
> LEFT JOIN (
> SELECT 10 AS VAL FROM rdb$database) T2
> ON T1.VAL=T2.VAL
>
> If you execute the above SQL in an analyzer, you will see that rdb$database is read 8 times.
>
> How can I write the SQL to read only 5 records? I need to use two nested tables for design
> questions and the LEFT JOIN operator because not all right records have correspondence in
> the left records.
>
> Firebird seems to read the right side once for each record in the left side.
> Is there a way to evaluate only ONCE the right side and then to re-use it?

First, thanks for simplifying your problem. However, it might be too simplified - I can understand why Firebird uses 4 lookups against T2 (it has to check whether it is equal to 1, 2, 3 and 4 respectively), but I neither see how it can be done differently nor that there is a problem with it.

What you could try, of course, is to use CTEs:

with T1 as
(SELECT 1 AS VAL FROM rdb$database
UNION SELECT 2 FROM rdb$database
UNION SELECT 3 FROM rdb$database
UNION SELECT 4 FROM rdb$database
),
T2 as
(SELECT 10 AS VAL FROM rdb$database T3
JOIN T1 ON T1.VAL = 10
)
select *
from t1
left join t2
ON T1.VAL=T2.VAL

(I added JOIN T1 as part of the CTE T2 to also show an example on how it is possible to use an inner join in a CTE even though you want a left join - haven't seen situations where this is useful, but think it can be).

There won't be any benefit of using CTEs in this particular example, but I wouldn't be surprised if it could make a difference in your real case (assuming you have WHERE clauses as well).

HTH,
Set