but LEFT JOIN is diferente of JOIN...
- LEFT JOIN brings all the data filtered from the FROM table, and
- JOIN brings only the data that have the ON condition in the JOINED table.

Att,

Alexis Diel

On Fri, Aug 2, 2013 at 3:43 AM, Josef Kokeš <j.kokes@...>wrote:

> 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
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>


[Non-text portions of this message have been removed]