MYSQL Outer Join Simplification

The outer join in the query from statement can be simplified in many cases;

In the parsing phase, the right outer JOIN operation can be turned into an ode value containing the left JOIN operation, in general, the transition:

 Right JOIN  on =  leftJOIN on P (T1,..., T2,...)

All inner join expressions such as (T1 inner join T2 on P (t1,t2)) can be replaced (T1,t2,p (T1,T2)) (condition of two tables by where conjunct or nested join conditions);

When the optimizer evaluates a join query execution plan with a outer JOIN operation, it only considers these cases, and the appearance is accessed before the table;

e.g.

SELECT *  Left JOIN  on P1 (t1,t2)   WHERE  and R (T2)

R (T2) in where condition reduces the number of matching rows in many T2 tables, and if we execute this query, the optimizer will not hesitate to access the T1 table first, and then this will result in a very inefficient execution plan;

Fortunately, if where condition is null-rejected,mysql, a query like this will be turned into a non-outer JOIN operation. For a outer join operation, for any null-populated row, if Null-rejected's condition is judged to be false or unknown ( that is, e.g. for left join, not left table, A null value is used to populate the row data, when the condition can determine that the operation is not valid );

e.g.

 Left JOIN  on T1. A=T2. A

The conditions for null-rejected are:

 is  not NULL  >3 <= < 2 OR > 1

For non-null-rejected conditions:

 is NULL  <3ORis notNULL<3  OR>3

The general condition of the null-rejected condition for the outer JOIN operation is relatively simple:

1: If it is a was not null form, when a is an attribute of any internal table ;

2: If a connection contains a null-rejected condition as a connection condition ;

3: If he is a divisive null-rejected condition ;

In a OUTER JOIN operation query, a null-rejected condition may be not null-rejected for the other table:

SELECT *  from  Left JOIN  on T2. A=T1. A                 leftJOIN on T3. B=T1. B  WHERE>0

The Where condition is a null-rejected condition for the second outer join operation, but not for the first one.

if the Where condition is a null-rejected condition for a outer JOIN operation, then the outer JOIN operation can be converted to a inner JOIN operation ;

SELECT *  from  Left JOIN  on T2. A=T1. A                 INNERJOIN on T3. B=T1. B  WHERE>0

For the original query, the optimizer will t1,t2,t3 the Apple query plan through a unique access order, and for the above alternative query, he may consider t3,t1,t2

A transition of a outer JOIN operation may trigger another change:

SELECT *  from  Left JOIN  on T2. A=T1. A                 leftJOIN on T3. B=T2. B  WHERE>0

First

SELECT *  from  Left JOIN  on T2. A=T1. A                 INNERJOIN on T3. B=T2. B  WHERE>0

is equivalent to:

SELECT *  from  Left JOIN  on T2. A=T1. A), T3  WHERE>0 and t3.b=T2. B

The remaining outer JOIN operation can now be replaced with a inner join, because the condition t3.b= t2.b is a null-rejected, so we get a query without a outer join

SELECT *  from INNER JOIN  on T2. A=T1. A), T3  WHERE>0 and t3.b=T2. B

Sometimes we can replace a nested outer JOIN operation, but it is not possible to change nested outer join;

SELECT *  from  Left JOIN Left joins on T3.                 B=T2. B)              on T2. A=T1. A  WHERE>0

To be replaced by:

SELECT *  from  Left JOIN INNER JOIN on T3.                 B=T2. B)              on T2. A=T1. A  WHERE>0,

Can be rewritten to still contain nested outer JOIN operations:

SELECT *  from  Left JOIN               (T2,T3)              on (T2. A= and T3. B=T2. B)  WHERE>0.

When trying to transform a nested outer JOIN operation, you must consider the join condition and where conditions of the nested outer join:

SELECT *  fromT1 Left JOIN(T2 Left JOINT3 onT3. B=T2. B) onT2. A=T1. A andT3. C=T1. CWHERET3. D> 0 ORT1. D> 0

The Where condition is not a null-rejected condition for a nested outer join, but the join condition of a nested outer join T2.A=T1.A AND T3.C=T1.C is a null-rejected condition, so equivalent to:

SELECT *  from  Left JOIN               (T2, T3)              on T2. A= and T3. C= and T3. B=T2. B  WHERE>0OR>0

MYSQL Outer Join Simplification