Optimizing SELECT Statements 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. Outer joins include  LEFT JOIN  and  RIGHT JOIN . MySQL implements an  A  LEFT JOIN  B join_specification  as follows: 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 : •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. 8.2.1.9 Outer Join Optimization 1667