--- 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

This is, in most cases, due to abuse of the LEFT JOIN syntax (in my
opinion). First, you tell the optimizer to include all records from
TableA whether or not there exists a match in TableB, and then you say
that a field in TableB has to have a certain value. Of course,
TableB.Myfield will not have any value for records in TableA that does
not match any record in TableB and the results will be identical to a
plain INNER JOIN. Result: The optimizers' choice is limited and
programmers get confused.

Set