MySQL 8.4 Release Notes
Outer joins include LEFT JOIN and
RIGHT JOIN.
MySQL implements an A LEFT
JOIN B
join_specification
Table
Bis set to depend on tableAand all tables on whichAdepends.Table
Ais set to depend on all tables (exceptB) that are used in theLEFT JOINcondition.The
LEFT JOINcondition is used to decide how to retrieve rows from tableB. (In other words, any condition in theWHEREclause 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
WHEREoptimizations are performed.If there is a row in
Athat matches theWHEREclause, but there is no row inBthat matches theONcondition, an extraBrow is generated with all columns set toNULL.If you use
LEFT JOINto find rows that do not exist in some table and you have the following test:col_nameIS NULLWHEREpart, wherecol_nameis a column that is declared asNOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches theLEFT JOINcondition.
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 the
WHERE condition is always false for the
generated NULL row, the LEFT
JOIN is changed to an inner join. For example, the
WHERE clause would be false in the
following query if t2.column1 were
NULL:
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 the
WHERE 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_2Now 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; see
Section 10.9.3, “Optimizer Hints”. Alternatively, use
STRAIGHT_JOIN; see
Section 15.2.13, “SELECT Statement”. However,
STRAIGHT_JOIN may prevent indexes from
being used because it disables semijoin transformations; see
Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations.