Movatterモバイル変換

Table expressions in theFROM clause of a query are simplified in many cases.

At the parser stage, queries with right outer join operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed such that this right join:

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

Becomes this equivalent left join:

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

All inner join expressions of the formT1 INNER JOIN T2 ON P(T1,T2) are replaced by the listT1,T2,P(T1,T2) being joined as a conjunct to theWHERE condition (or to the join condition of the embedding join, if there is any).

When the optimizer evaluates plans for outer join operations, it takes into consideration only plans where, for each such operation, the outer tables are accessed before the inner tables. The optimizer choices are limited because only such plans enable outer joins to be executed using the nested-loop algorithm.

Consider a query of this form, whereR(T2) greatly narrows the number of matching rows from tableT2:

SELECT * T1 FROM T1  LEFT JOIN T2 ON P1(T1,T2)  WHERE P(T1,T2) AND R(T2)

If the query is executed as written, the optimizer has no choice but to access the less-restricted tableT1 before the more-restricted tableT2, which may produce a very inefficient execution plan.

Instead, MySQL converts the query to a query with no outer join operation if theWHERE condition is null-rejected. (That is, it converts the outer join to an inner join.) A condition is said to be null-rejected for an outer join operation if it evaluates toFALSE orUNKNOWN for anyNULL-complemented row generated for the operation.

Thus, for this outer join:

T1 LEFT JOIN T2 ON T1.A=T2.A

Conditions such as these are null-rejected because they cannot be true for anyNULL-complemented row (withT2 columns set toNULL):

T2.B IS NOT NULLT2.B > 3T2.C <= T1.CT2.B < 2 OR T2.C > 1

Conditions such as these are not null-rejected because they might be true for aNULL-complemented row:

T2.B IS NULLT1.B < 3 OR T2.B IS NOT NULLT1.B < 3 OR T2.B > 3

The general rules for checking whether a condition is null-rejected for an outer join operation are simple:

A condition can be null-rejected for one outer join operation in a query and not null-rejected for another. In this query, theWHERE condition is null-rejected for the second outer join operation but is not null-rejected for the first one:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 LEFT JOIN T3 ON T3.B=T1.B  WHERE T3.C > 0

If theWHERE condition is null-rejected for an outer join operation in a query, the outer join operation is replaced by an inner join operation.

For example, in the preceding query, the second outer join is null-rejected and can be replaced by an inner join:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 INNER JOIN T3 ON T3.B=T1.B  WHERE T3.C > 0

For the original query, the optimizer evaluates only plans compatible with the single table-access orderT1,T2,T3. For the rewritten query, it additionally considers the access orderT3,T1,T2.

A conversion of one outer join operation may trigger a conversion of another. Thus, the query:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 LEFT JOIN T3 ON T3.B=T2.B  WHERE T3.C > 0

Is first converted to the query:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 INNER JOIN T3 ON T3.B=T2.B  WHERE T3.C > 0

Which is equivalent to the query:

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3  WHERE T3.C > 0 AND T3.B=T2.B

The remaining outer join operation can also be replaced by an inner join because the conditionT3.B=T2.B is null-rejected. This results in a query with no outer joins at all:

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3  WHERE T3.C > 0 AND T3.B=T2.B

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

Any attempt to convert an embedded outer join operation in a query must take into account the join condition for the embedding outer join together with theWHERE condition. In this query, theWHERE condition is not null-rejected for the embedded outer join, but the join condition of the embedding outer joinT2.A=T1.A AND T3.C=T1.C is null-rejected:

SELECT * FROM T1 LEFT JOIN              (T2 LEFT JOIN T3 ON T3.B=T2.B)              ON T2.A=T1.A AND T3.C=T1.C  WHERE T3.D > 0 OR T1.D > 0

Consequently, the query can be converted to:

SELECT * FROM T1 LEFT JOIN              (T2, T3)              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B  WHERE T3.D > 0 OR T1.D > 0