I was trying to interpret the relationship between JOINs like LEFT-OUTER JOIN, RIGHT-OUTER JOIN, etc with relationships like Many-To-Many, One-To-Many etc in Hibernate or database language.
Meaning of JOINs in sql
hibernatejoin;
Related Solutions
The join order appears unimportant to the query final result given your mapped table equalities.
The order of table sources after the FROM keyword does not affect the result set that is returned.
That said, you could, if order is very important to your process, or results, consider nested Common Table Expressions
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
Related Question
- MySQL Many-to-Many Join – How to Perform Many-to-Many Join with Left Join
- SQL Server – Understanding Query Motivation for Cross Joins
- Sql-server – changing from inner joins to left joins to include null values
- SQL Server – Resolving ‘No Join Predicate’ Warning with Outer Join
- SQL – How to Use Outer Join with Extra Column Condition
Best Answer
Relationships are used for describing how the tables are well, related and for enforcing this such as satisfying a foreign key from one table requires a matching entry in another. A join is used to realize those relationships. In a join you are telling the database what fields to match and how when it is executed. In a relationship you are telling the database to enforce this for future queries. Also joins are used in gathering data, whereas relationships are meant to maintain data integrity from update anomalies as well as used for conceptual reasons for developers and DBAs to know how the tables are meant to be used.