Understanding Driving (Outer) Tables in MySQL Join Algorithms

MySQL 8.0.18 introduced hash join as an optimization of Block Nested‑Loop, and from 8.0.20 the Block Nested‑Loop algorithm was removed, making hash join the default join method.

Nested‑Loop algorithm processes the outer (driving) table row by row and for each row scans the inner table. The outer table is called the outer table, and the inner table is the inner table.

Example code for a basic Nested‑Loop join:

for each row in t1 matching range {
for each row in t2 {
if row satisfies join conditions, send to client
}
}

The outer table drives the inner table; therefore the outer table is effectively the driving table . Oracle’s definition of a driving table matches this interpretation.

Block Nested‑Loop (BNL) improves the basic Nested‑Loop by buffering multiple rows from the outer table in a join buffer, reducing the number of full scans of the inner table. If the join buffer can hold all outer rows, the inner table is scanned only once.

Hash Join further optimizes BNL by maintaining a hash table in the join buffer, allowing constant‑time lookups instead of linear scans, thus lowering CPU cost.

Choosing the outer (driving) table depends on indexes and row cardinality. For indexed columns with high cardinality, an Index Nested‑Loop is preferred and the smaller table is chosen as the outer table. Without indexes, the optimizer may select BNL or hash join, still preferring the smaller table to fit the join buffer. However, the optimizer may choose a larger table as outer if it has an index that makes the inner scan cheaper.

Other databases (Informix, Sybase, Oracle) use similar terminology: the first table scanned in a nested‑loop join is the outer (driving) table, and the second is the inner table. In outer joins, the left table is the outer table, and the right table is the inner table.

Overall, the outer table is the driving table in join processing, whether the algorithm is Nested‑Loop, Block Nested‑Loop, or Hash Join, and its selection is guided by the optimizer based on indexes, row counts, and memory constraints.