What is the difference between these joins?

  1. a left join b left join c
  2. (a left join b) left join c
  3. a left join (b left join c)

Does the grouping only affect the order of the joins? Will the query engine decompose #1 into one of #2 or #3, i.e. pick an order for doing the joins?

I'm asking because h2 uses indexes on c for #1, but for #2 does a table scan of c.

Here b is a simple many-to-many join table with columns (a_id, c_id).

  • You forgot the ON clauses which clarify a bit why parenthesis are not needed. But, to answer your question: 1 and 2 are equivalent. The results will be the same. The 3rd is not logically equivalent to the first two and may produce different results. So, it's very likely that different execution plan will be made. – ypercubeᵀᴹ Apr 20 '13 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.