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.