Transitive Predicates

The transitive property of numbers states that if A = B and B = C, then A = C. This property can be applied to queries:

SELECT     *

FROM        part, partsupp, lineitem

WHERE        ps_partkey = l_partkey and l_partkey = p_partkey and

                ps_availqty > l_quantity and ps_supplycost > p_retailprice

Because both ps_partkey and p_partkey are equal to l_partkey, ps_partkey must be equal to p_partkey. The query processor takes advantage of this by deriving the third join predicate (ps_partkey equal to p_partkey). For example, in this query, the query processor begins by joining the partkey in the parts table to the partkey in the partsupp table, even though this particular join predicate is never specified in the query. It can do so because of transitive predicates.