The MySQL documentation here suggests the following:
Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:
SELECT * FROM T1 LEFT JOIN (T2 LEFT JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0
Is converted to:
SELECT * FROM T1 LEFT JOIN (T2 INNER JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0
That can be rewritten only to the form still containing the embedding outer join operation:
SELECT * FROM T1 LEFT JOIN (T2,T3) ON (T2.A=T1.A AND T3.B=T2.B) WHERE T3.C > 0
Why can not we convert the 'embedding' left outer join in this case into inner as well? Since
T3.C > 0 is null-rejecting,
T3.B=T2.B becomes null-rejecting. Consequently shouldn't
T2.A=T1.A also be null-rejecting?
Overall, can't the embedded query can be treated like below?
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0
Is there a reason behind this? It is just another form of writing the query right? Why is it being treated like subquery?