Transitive joins
I'm trying to understand what the term "transitive join" means. Could you please explain, and perhaps give a simple example?
Literally, a transitive join is a join that "goes across" (if I remember my high school Latin, which was several decades ago). Merriam-Webster's second definition of transitive is:
being or relating to a relation with the property that if the relation holds between a first element and a second and between the second element and a third, it holds between the first and third elements
<equality is a transitive relation>
So in a transitive join, if rows of table A are joined to rows of table B, and those rows of table B are joined to rows of table C, then the rows of A could also be joined to rows of C, assuming, of course, that the same columns of B join to A and to C.
In fact, a good database optimizer may actually "throw in" the additional join clauses to join A to C, thus allowing more efficient paths to the final result set. An example of this is described in Join transitive closure on the Sybase Web site.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
