# Transitive closure on join clauses

When a join statement selects from three or more tables, Derby analyzes any equijoin predicates between simple column references within each query block and adds additional equijoin predicates where possible if they do not currently exist.

See equijoin predicate and simple column reference for definitions. For example, Derby transforms the following query:

    SELECT * FROM samp.employee e, samp.emp_act a, samp.emp_resume r
    WHERE e.empno = a.empno
    and a.empno = r.empno

into the following:

    SELECT * FROM samp.employee e, samp.emp_act a, samp.emp_resume r
    WHERE e.empno = a.empno
    and a.empno = r.empno
    and e.empno = r.empno

On the other hand, the optimizer knows that one of these equijoin predicates is redundant and will throw out the one that is least useful for optimization.

Parent topic: Transitive closure
Related reference
Transitive closure on search clauses