-> WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.col1 = t2.col1)\G *************************** 1. row *************************** EXPLAIN: -> Nested loop antijoin -> Table scan on t2 (cost=0.85 rows=6) -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c1=t2.c1) -> Materialize with deduplication -> Filter: (t1.c1 is not null) (cost=0.85 rows=6) -> Table scan on t1 (cost=0.85 rows=6) 

• Left outer join : 

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Left hash join (t2.c1 = t1.c1) (cost=3.99 rows=36) -> Table scan on t1 (cost=0.85 rows=6) -> Hash -> Table scan on t2 (cost=0.14 rows=6) 

• Right outer join  (observe that MySQL rewrites all right outer joins as left outer joins): 

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Left hash join (t1.c1 = t2.c1) (cost=3.99 rows=36) -> Table scan on t2 (cost=0.85 rows=6) -> Hash -> Table scan on t1 (cost=0.14 rows=6) 

By default, MySQL 8.0.18 and later employs hash joins whenever possible. It is possible to control whether hash joins are employed using one of the BNL  and NO_BNL  optimizer hints. 

(MySQL 8.0.18 supported hash_join=on  or hash_join=off  as part of the setting for the optimizer_switch  server system variable as well as the optimizer hints HASH_JOIN  or NO_HASH_JOIN . In MySQL 8.0.19 and later, these no longer have any effect.) 

Memory usage by hash joins can be controlled using the join_buffer_size  system variable; a hash join cannot use more memory than this amount. When the memory required for a hash join exceeds the amount available, MySQL handles this by using files on disk. If this happens, you should be aware that the join may not succeed if a hash join cannot fit into memory and it creates more files than set for open_files_limit . To avoid such problems, make either of the following changes: 

•Increase join_buffer_size  so that the hash join does not spill over to disk. 

•Increase open_files_limit . 

Beginning with MySQL 8.0.18, join buffers for hash joins are allocated incrementally; thus, you can set join_buffer_size  higher without small queries allocating very large amounts of RAM, but outer joins allocate the entire buffer. In MySQL 8.0.20 and later, hash joins are used for outer joins (including antijoins and semijoins) as well, so this is no longer an issue. 

## 8.2.1.5 Engine Condition Pushdown Optimization 

This optimization improves the efficiency of direct comparisons between a nonindexed column and a constant. In such cases, the condition is “pushed down”  to the storage engine for evaluation. This optimization can be used only by the NDB  storage engine. 

For NDB Cluster, this optimization can eliminate the need to send nonmatching rows over the network between the cluster’s data nodes and the MySQL server that issued the query, and can speed up queries where it is used by a factor of 5 to 10 times over cases where condition pushdown could be but is not used. 

Suppose that an NDB Cluster table is defined as follows: 

CREATE TABLE t1 ( 

