What is the difference between a nested loop join and a hash join? Does MySQL support hash joins?

• Supported in MySQL 8.0.18+ for equality-based INNER JOINs.

• Builds an in-memory hash table of the smaller table on the join key.

• Probes the hash table for each row in the larger table.

• Much faster than nested loops on large, unindexed tables for equality joins.

• Not used for range-based joins (>, <, BETWEEN).

• Algorithm: Nested loop scans rows sequentially; hash join builds and probes a hash table.

• Performance: Hash joins are faster for large tables without indexes; nested loops are efficient for indexed joins or small tables.

• Use Case: Nested loops are general-purpose; hash joins are optimal for equality joins on large datasets.

• Memory: Hash joins require memory for the hash table; nested loops can be memory-light if using indexes.