--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Adam" wrote:
> > Remember, the more logical join is the join which deals with the
> > records with no matching records in the other table in the way you
> > require it. They are either excluded (join) or they are included from
> > one table but not the other (left join / right join) or they are
> > included in both tables (full outer join). Which join to use is NOT a
> > question of performance, but of resultsets.
>
> What you write is true, Adam, I just wanted to add one possible

scenario.

>
> SELECT A.*
> FROM TableA A
> JOIN TableB B on B.TableA_ID = A.ID
> WHERE B.MyField = :Param
>
> will always produce the same result set as
>
> SELECT A.*
> FROM TableA A
> LEFT JOIN TableB B on B.TableA_ID = A.ID
> WHERE B.MyField = :Param
>

Argh, the poor execution plan. This will force Firebird to look
through every TableA record!

The first query is much better because it will in all probability
choose to use tableB first, and only do indexed reads from tableA.
This is of course subject to the availability of good indexing.

If you have an index on B.MyField and the Foreign key for B.TableA_ID
is reasonably selective, then the second query will be significantly
slower.

I suppose what I was trying to get across is that the differing join
syntaxes are not about getting better performance. I mean if there was
one that was the quickest and they were all interchangable, then it
would be silly to use anything else, ever. Instead, they are about
what you want to do with records in either table with no matching
record in the other table.

> This is, in most cases, due to abuse of the LEFT JOIN syntax (in my
> opinion).

Yeah. There are times when you need to play games with the optimiser.
The Firebird implementation of FK constraints can cause indices with
really poor selectivity, and you often need to add 0 to the join
fields to trick it into avoiding the index.

But I can not think of any case where using the left join in this
manner would improve performance.

Adam