MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.9 Outer Join Optimization (original) (raw)

10.2.1.9 Outer Join Optimization

Outer joins include LEFT JOIN andRIGHT JOIN.

MySQL implements an _`A`_ LEFT JOIN _`B`_ _`joinspecification`_ as follows:

• 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 theON 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: _`colname`_ IS NULL in the WHERE part, where_colname_ 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.

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 10.2.1.10, “Outer Join Simplification”.

For a LEFT JOIN, if theWHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to an inner join. For example, theWHERE clause would be false in the following query if t2.column1 wereNULL:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

Therefore, it is safe to convert the query to an inner join:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

Trivial WHERE conditions arising from constant literal expressions are removed during preparation, rather than at a later stage in optimization, by which time joins have already been simplified. Earlier removal of trivial conditions allows the optimizer to convert outer joins to inner joins; this can result in improved plans for queries with outer joins containing trivial conditions in theWHERE clause, such as this one:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1

The optimizer now sees during preparation that 0 = 1 is always false, making OR 0 = 1 redundant, and removes it, leaving this:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2

Now the optimizer can rewrite the query as an inner join, like this:

SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2

Now the optimizer can use table t2 before table t1 if doing so would result in a better query plan. To provide a hint about the table join order, use optimizer hints; seeSection 10.9.3, “Optimizer Hints”. Alternatively, useSTRAIGHT_JOIN; seeSection 15.2.13, “SELECT Statement”. However,STRAIGHT_JOIN may prevent indexes from being used because it disables semijoin transformations; seeOptimizing IN and EXISTS Subquery Predicates with Semijoin Transformations.