Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.