SQL Joins and Relationships

Question

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?

1. if relationship is "one-to-one" then "Inner join" is the best.
2. if relationship is "one-to-many" then ?
3. if relationship is "many-to-one" then ?
4. if relationship is "many-to-many" then left join be the best.

Answer 1

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 joins reduce the number of results and outer joins allow more results in comparison. Both situations can be useful.

Generally speaking inner joins should be preferred to outer joins due 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 for left outer joins over right 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