I need to use the LEFT JOIN in SQL and I find that the perfomance 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?

Thank you