--- In firebird-support@yahoogroups.com, "kokok_kokok" <kokok_kokok@...> wrote:

>
> 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
>

try this

SELECT * FROM

(
SELECT 1 AS VAL FROM rdb$database A
UNION
SELECT 2 FROM rdb$database B
UNION
SELECT 3 FROM rdb$database C
UNION
SELECT 4 FROM rdb$database D
) AS T1
INNER JOIN
(
SELECT 10 AS VAL FROM rdb$database E

) T2
ON 1=1
WHERE
T1.VAL=T2.VAL

Karol Bieniaszewski