2

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?

2
  • I suspect the other LEFT JOIN could be turned into INNER JOIN, too – Rick James Oct 15 '19 at 3:34
  • Please file a bug report with bugs.mysql.com. – Rick James Oct 15 '19 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.