I am confused by joins. I want to know how to choose the join based on the relationship between tables, or do joins have no dependency on relationship?
- if relationship is "one-to-one" then "Inner join" is the best.
- if relationship is "one-to-many" then ?
- if relationship is "many-to-one" then ?
- if relationship is "many-to-many" then left join be the best.
Best Answer
Instead of thinking about "type of join based relationship multiplicity" you should start thinking about the type of join based on what you want to happen if there isn't a corresponding relationship. This is a much more useful place to start.
See this post by Jeff Atwood. As you can see in the sample results next to his queries,
inner joinsreduce the number of results andouter joinsallow more results in comparison. Both situations can be useful.Generally speaking
inner joinsshould be preferred toouter joinsdue to performance reasons, but the driving force for which type of join needs to be what you want to happen if there isn't a match. Also in my experience there is a general preference forleft outer joinsoverright outer joins.As with anything in life there are caveats and gotchas, but this is a much better place to start.
Another useful visualization: SQL joins are easy