In MySQL 5.6, the original implementation of the BNL algorithm is extended to support outer join and semi-join operations.
When these operations are executed with a join buffer, each row put into the buffer is supplied with a match flag.
If an outer join operation is executed using a join buffer,
each row of the table produced by the second operand is
checked for a match against each row in the join buffer. When
a match is found, a new extended row is formed (the original
row plus columns from the second operand) and sent for further
extensions by the remaining join operations. In addition, the
match flag of the matched row in the buffer is enabled. After
all rows of the table to be joined have been examined, the
join buffer is scanned. Each row from the buffer that does not
have its match flag enabled is extended by
NULL complements (
values for each column in the second operand) and sent for
further extensions by the remaining join operations.
As of MySQL 5.6.3, the
flag of the
system variable controls how the optimizer uses the Block
Nested-Loop algorithm. By default,
See Section 188.8.131.52, “Controlling Switchable Optimizations”.
Before MySQL 5.6.3, the
system variable controls join buffer management. For the
possible values of this variable and their meanings, see the
description in Section 5.1.4, “Server System Variables”.
For information about semi-join strategies, see Section 184.108.40.206, “Optimizing Subqueries with Semi-Join Transformations”