Hi!

For a long time I have been using LEFT JOINs rather than JOINs
everywhere, because of some past experience with Firebird 1.5 that LEFT
JOIN is much faster than JOIN, even if I have to use WHERE
key_of_the_joined_table IS NULL afterwards. Today I encountered a case
where this is the case even with both Firebird 2.1 and 2.5. What could
possibly be the cause? Should I stick to LEFT JOINs?

The query is:

SELECT MAX(date_of_operation)
FROM table1
[LEFT] JOIN view1 ON view1.foreign_key=table1.primary_key
WHERE table1.some_field=1234

with view1 defined as a simple sequence of LEFT JOINs:

SELECT ...
FROM table2
LEFT JOIN table3 ON table3.primary_key=table2.foreign_key1
LEFT JOIN table4 ON table4.primary_key=table2.foreign_key2
...
LEFT JOIN tableX ON tableX.primary_key=table4.foreign_key

The LEFT JOIN version takes some milliseconds to perform, the JOIN
version takes over 25 seconds. The difference in plans is, as far as I
can tell, that the LEFT JOIN version first selects the relevant records
from table1 and then joins the matching records from view1 to it, while
the JOIN version first builds the view1 (all records), then joins table1
to it, and then performs the WHERE:

LEFT JOIN: PLAN JOIN (table1 INDEX (index_fk1), JOIN(JOIN(...)))
JOIN: PLAN JOIN(JOIN(JOIN(...)), table1 INDEX (index_pk))

(Note: all the joins are indexed)

In this case I will simply rewrite the query to use the LEFT JOIN and
probably keep using LEFT JOINs in all future queries, even if the JOIN
might seem more suitable to the query, but I am really curious why is
there such a difference in the optimizer.

Thanks,

Josef