such that P1(t1,t2) AND (f1?C2(t2):TRUE) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1 && P(t1,NULL,NULL)) { t:=t1||NULL||NULL; OUTPUT t; } } 

In general, pushed-down predicates can be extracted from join conditions such as P1(T1,T2)  and P(T2,T3) . In this case, a pushed-down predicate is guarded also by a flag that prevents checking the predicate for the NULL -complemented row generated by the corresponding outer join operation. 

Access by key from one inner table to another in the same nested join is prohibited if it is induced by a predicate from the WHERE  condition. 

## 8.2.1.9 Outer Join Optimization 

Outer joins include LEFT JOIN  and RIGHT JOIN . 

MySQL implements an A  LEFT JOIN B join_specification  as follows: 

•Table B  is set to depend on table A  and all tables on which A  depends. 

•Table A  is set to depend on all tables (except B ) that are used in the LEFT JOIN  condition. 

•The LEFT JOIN  condition is used to decide how to retrieve rows from table B . (In other words, any condition in the WHERE  clause is not used.) 

•All standard join optimizations are performed, with the exception that a table is always read after all tables on which it depends. If there is a circular dependency, an error occurs. 

•All standard WHERE  optimizations are performed. 

•If there is a row in A  that matches the WHERE  clause, but there is no row in B  that matches the ON condition, an extra B  row is generated with all columns set to NULL . 

•If you use LEFT JOIN  to find rows that do not exist in some table and you have the following test: col_name  IS NULL  in the WHERE  part, where col_name  is a column that is declared as NOT NULL , MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN  condition. 

The RIGHT JOIN  implementation is analogous to that of LEFT JOIN  with the table roles reversed. Right joins are converted to equivalent left joins, as described in Section 8.2.1.10, “Outer Join Simplification” . 

For a LEFT JOIN , if the WHERE  condition is always false for the generated NULL  row, the LEFT JOIN is changed to an inner join. For example, the WHERE  clause would be false in the following query if t2.column1  were NULL : 

