--- In firebird-support@yahoogroups.com, "tdtappe" <tappe@t...> wrote:

> Sorry for reposting this.
> But isn't there anybody who can tell something about it?
> For instance confirm it!?
>
> (see above topic, 9th August 2005)
>
> --Heiko

To make it easier to others

http://groups.yahoo.com/group/firebird-support/message/64784

Now of course you are aware that using any sort of left join almost
guarantees the optimiser will decide to do the "left" table first. I
have come across numerous cases where people have used left joins
because they don't understand the difference, but I take it this is
not the case here.


SELECT
A.PrimaryKeyCol, B.PrimaryKeyCol, C.PrimaryKeyCol
FROM
Table1 A
LEFT OUTER JOIN Table2 B ON A.ForeignKeyColToTable2 = B.PrimaryKeyCol
LEFT OUTER JOIN Table3 C ON B.ForeignKeyColToTable4 =
C.ForeignKeyColToTable4

This will return consider all records in table1 even if there is no
matching field in table2. In cases where table2 doesn't match,
B.ForeignKeyColToTable4 will be NULL. If table3 has nulls in it, then
you are going to get a lot of matches (as I think you observed). The
more NULLs you get, the worse the performance will get.

Now it is late and my brain is off, so someone else can correct me :),
but have you tried testing for the null in the join, like:

SELECT
A.PrimaryKeyCol, B.PrimaryKeyCol, C.PrimaryKeyCol
FROM
Table1 A
LEFT OUTER JOIN Table2 B ON (A.ForeignKeyColToTable2 = B.PrimaryKeyCol
and A.ForeignKeyColToTable2 is not null)
LEFT OUTER JOIN Table3 C ON (B.ForeignKeyColToTable4 =
C.ForeignKeyColToTable4 and B.ForeignKeyColToTable4 is not null)

If you haven't already done so, get yourself a query analysing tool as
the graphical representation of table reads can tell you in 5 seconds
what can take a few minutes to otherwise comprehend.

Adam