Sorry for that sample
but i see more wrong with this

the resultset is wrong!
SELECT * FROM
(
SELECT 1 AS VAL FROM rdb$database
) T1
LEFT JOIN
(
SELECT 10 AS VAL FROM rdb$database
) T2
ON T1.VAL=T2.VAL

result 1 10
but should be 1 NULL !!

but this was fixed in FB 2.5
look at http://tracker.firebirdsql.org/browse/CORE-1246

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