MySQL implements a
as
follows:
A
LEFT
JOIN B
join_condition
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 dependence, MySQL issues an error.
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 the
ON
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:
in the col_name
IS
NULLWHERE
part, where
col_name
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 implementation of RIGHT JOIN
is analogous
to that of LEFT JOIN
with the roles of the
tables reversed.
The join optimizer calculates the order in which tables should
be joined. The table read order forced by LEFT
JOIN
or STRAIGHT_JOIN
helps the
join optimizer do its work much more quickly, because there are
fewer table permutations to check. Note that this means that if
you do a query of the following type, MySQL does a full scan on
b
because the LEFT JOIN
forces it to be read before d
:
SELECT * FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
The fix in this case is reverse the order in which
a
and b
are listed in the
FROM
clause:
SELECT * FROM b JOIN a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
Starting from 4.0.14, for a LEFT JOIN
, if
the WHERE
condition is always false for the
generated NULL
row, the LEFT
JOIN
is changed to a normal 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 a normal join:
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
This can be made faster because MySQL 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
STRAIGHT_JOIN
. (See
Section 12.2.7, “SELECT
Syntax”.)
User Comments
When using more than one left join, the first occurrence of NULL will cause latter fields to be reported NULL with the USING(field) clause.
instead of :
t1 LEFT JOIN t2 USING (id) LEFT JOIN t3 USING (id)
use:
t1 LEFT JOIN t2 ON (t1.id = t2.id) LEFT JOIN t3 ON (t1.id=t3.id)
Outer joins can work inefficiently when joining columns are in different character sets.
In my case query worked very slow when "the outer" table had utf8 joining columns, and a second table had latin1 ones.
Though, query worked fast when the outer table had latin1 joining columns, and a second table had utf8 ones.
Join was efficient in any direction when columns that tables outer joined were the same character set, ether both latin1, or both utf8.
Add your own comment.