Predicates in ON clauses are conceptually evaluated before the WHERE clause. Within the FROM clause, predicates in the ON clauses are evaluated in the order the joins are executed —inner most (deepest) first, and then left to right. For example, in the following FROM clause, T1 is joined to T2, T3 joined to T4, and then the result of T1 and T2 joined to the result of T3 and T4:
FROM (T1 left join T2 on t1.c1 = t2.c1) left join
(T3 left join T4 on t3.c1 = t4.c1) on T2.c1 = T4.c1
If a non-matching row has caused the columns of the non-preserved row to be set to null in a previous join, then unless IS NULL is the predicate, the result is unknown. In the previously shown example, if either of the first two joins produces a non-matching row, then the T2.c1 = T4.c1 predicate evaluates as unknown, and columns in T3 and T4 are set to the null value.
Since the WHERE clause is evaluated last, a predicate other than IS NULL on a column that has been set to the null value in an outer join causes the result row to be rejected. This effectively changes the outer join to an inner join, since any preserved rows are rejected. Continuing the example, WHERE T4.c2 = 'xxx' effectively converts both left joins to inner joins.
|
Copyright © 2014 CA.
All rights reserved.
|
|