Thank you but it does not work for me. Not all right side records have the corresponding record in the left side. For this reason, I cannot use INNER JOIN. I need to use LEFT JOIN.

--- In firebird-support@yahoogroups.com, "karolbieniaszewski" <liviuslivius@...> wrote:
>
>
> --- 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
>