>my friend and collegue Frank (fsg) and I kept in mind, that at the prior to >the last FB-conference, we listend to an optimization talk >of one of the core devs (it must have been Dmitry ;-) ) and remembered that: > >- engine does not reorder joins for optimization purpose if left join and >straight join get mixed >- engine evaluates join before left join. > >This was, what made me assume, the expression would be evaluated like the >braced version. I did not check SQL standard definition yet, >but it seemed somewhat natural to me...
I don't think your query can be considered an 'optimization issue'. I can see how something like select i.I, j.I, k.I from CTE_CNT i left join CTE_CNT j on i.I = j.I and j.I<10 join CTE_CNT k on i.I = k.I can be optimized to (although Firebird doesn't do this, which I assume was Dmitrys point) select i.I, j.I, k.I from CTE_CNT i join CTE_CNT k on i.I = k.I left join CTE_CNT j on i.I = j.I and j.I<10 Optimizing your query to select i.I, j.I, k.I from CTE_CNT i join CTE_CNT k on j.I = k.I left join CTE_CNT j on i.I = j.I and j.I<10 would simply lead to an error (j is not defined at time of join). Firebird accepts that you first try to do a left join and then straight afterwards change your mind with another inner join. Basically, I guess (don't know) it behaves similar to how it would have done if you had written: select i.I, j.I, k.I from CTE_CNT i left join CTE_CNT j on i.I = j.I left join CTE_CNT k on j.I = k.I where j.I<10 I cannot say whether your or Firebirds way of thinking is most appropriate according to how SQL ought to be implemented (and I don't know the SQL standard), I'm too Firebirdy to give any unbiased opinion. Set
