Outer joins include LEFT JOIN
and RIGHT JOIN
.
外部连接包括左连接和右连接。
MySQL implements an
as follows:A
LEFT JOIN B
join_specification
MySQL实现了一个A LEFT JOIN B join_specification如下:
-
Table
B
is set to depend on tableA
and all tables on whichA
depends. - 表B被设置为依赖于表A和A所依赖的所有表。
-
Table
A
is set to depend on all tables (exceptB
) that are used in theLEFT JOIN
condition. - 表A被设置为依赖于在LEFT JOIN条件中使用的所有表(B除外)。
-
The
LEFT JOIN
condition is used to decide how to retrieve rows from tableB
. (In other words, any condition in theWHERE
clause is not used.) -
LEFT JOIN条件用于决定如何从表b中检索行(换句话说,不使用WHERE子句中的任何条件)。
-
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. - 执行所有标准的WHERE优化。
-
If there is a row in
A
that matches theWHERE
clause, but there is no row inB
that matches theON
condition, an extraB
row is generated with all columns set toNULL
. - 如果a中有一行匹配WHERE子句,但B中没有一行匹配ON条件,则生成一个额外的B行,所有列都设置为NULL。
-
If you use
LEFT JOIN
to find rows that do not exist in some table and you have the following test:
in thecol_name
IS NULLWHERE
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. -
如果你使用了加入找到行不存在在一些表,你有以下测试:col_name部分是NULL, col_name在哪一列声明为not NULL, MySQL停止寻找更多的行(特定组合键)后发现一行左边的联接条件相匹配。
The RIGHT JOIN
implementation is analogous to that of LEFT JOIN
with the table roles reversed.
RIGHT JOIN的实现类似于LEFT JOIN的实现,只是表角色相反。
Right joins are converted to equivalent left joins, as described in Section 8.2.1.9, “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
:
对于LEFT JOIN,如果生成的NULL行的WHERE条件始终为false,则LEFT JOIN将更改为内部连接。例如,如果t2,在下面的查询中WHERE子句将为假。column1零:
Therefore, it is safe to convert the query to an inner join:
因此,将查询转换为内部连接是安全的:
Now the optimizer can use table t2
before table t1
if doing so would result in a better query plan.
现在,优化器可以在使用表t1之前使用表t2,如果这样做会产生更好的查询计划的话。
To provide a hint about the table join order, use STRAIGHT_JOIN
;
要提供关于表连接顺序的提示,请使用STRAIGHT_JOIN;
see Section 13.2.9, “SELECT Statement”.
However, STRAIGHT_JOIN
may prevent indexes from being used because it disables semijoin transformations;
但是,由于它禁用了半连接转换,因此可以阻止使用索引;