--- In firebird-support@yahoogroups.com, Radu Sky wrote:

> Between these two statement , which one is faster?
>
> SELECT C1,C2... FROM TableA
> JOIN TableB ON TableA.PK=TableB.FK
>
> or
>
> SELECT C1,C2... FROM TableA
> LEFT JOIN TableB ON TableA.PK=TableB.FK
>
> Both queries return the same result, FK is the Foreign key for PK

As Martijn said, these are different queries that accidentally may
return the same result. However, if you have records in TableA that
are not referenced in TableB, the results differ. Another way that
LEFT and INNER JOINs may produce the same result, is to use TableB in
the WHERE clause. In LEFT JOINs, the right table should only be
referenced in the WHERE clause if you want something like WHERE
TableB.PK IS NULL, all other cases that I've seen are only suitable
for confusing somebody.

That is, there is yet another exception - occasionally the optimizer
does not choose the optimal plan, and a LEFT JOIN leaves the optimizer
less choice, it has to work on the left table before linking to the
right table.

My general advice regarding INNER and OUTER JOINs, is to use [INNER]
JOIN whenever you can, and use LEFT [OUTER] JOIN when you have to.
LEFT JOIN is only ever faster than JOIN in cases where the optimizer
makes a mistake (otherwise, it is at best equally fast).

HTH,
Set