Documentation Home
MySQL 9.3 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 261.1Kb
Man Pages (Zip) - 368.3Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


10.2.1.9 Outer Join Optimization

Outer joins includeLEFT JOIN andRIGHT JOIN.

MySQL implements anA LEFT JOINBjoin_specification as follows:

  • TableB is set to depend on tableA and all tables on whichA depends.

  • TableA is set to depend on all tables (exceptB) that are used in theLEFT JOIN condition.

  • TheLEFT JOIN condition is used to decide how to retrieve rows from tableB. (In other words, any condition in theWHERE 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 standardWHERE optimizations are performed.

  • If there is a row inA that matches theWHERE clause, but there is no row inB that matches theON condition, an extraB row is generated with all columns set toNULL.

  • If you useLEFT JOIN to find rows that do not exist in some table and you have the following test:col_name IS NULL in theWHERE part, wherecol_name is 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 JOIN condition.

TheRIGHT JOIN implementation is analogous to that ofLEFT JOIN with the table roles reversed. Right joins are converted to equivalent left joins, as described inSection 10.2.1.10, “Outer Join Simplification”.

For aLEFT JOIN, if theWHERE condition is always false for the generatedNULL row, theLEFT JOIN is changed to an inner join. For example, theWHERE clause would be false in the following query ift2.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;

TrivialWHERE 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 ONcondition_1 WHEREcondition_2 OR 0 = 1

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

SELECT * FROM t1 LEFT JOIN t2 ONcondition_1 wherecondition_2

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

SELECT * FROM t1 JOIN t2 WHEREcondition_1 ANDcondition_2

Now the optimizer can use tablet2 before tablet1 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.