A hash join builds an in-memory hash table from one relational input, then hashes rows from the other input and compares matching buckets. It suits large tables that are not sorted by the join key, especially when input sizes differ. section 10.5
Mistake: forcing the apparently fastest operator
A query joining Marketing and MarketingArchive on ImpressionUID took 19 seconds with Nested Loops. Forcing INNER HASH JOIN reduced the test result to 5 seconds, but later indexes made Merge Join preferable at 3 seconds while the forced hash plan took 4 seconds. Treat a hash hint as a last resort; improve statistics or indexes first, and preserve optimizer choice where possible. section 10.5
Table 10.1 Physical join operators (view table figure)
| Operator |
Description |
Most efficient use |
|---|---|---|
| Nested Loops |
Choose one table as the outer input table and the other table becomes the inner input table. For each key in the outer input table, it searches every row in the inner input table for matching key values. |
Small tables |
| Hash Join |
Builds a hash table in memory, with each row of the first table placed in a hash bucket, dependent on the key value. The keys of the second table are then hashed and compared to the first table on a row-by-row basis. If there is not enough memory for every row in the first table, then the operation will be performed in multiple steps. |
Large tables not sorted by join key or where inputs have a big difference in row count |
| Merge Join |
Checks the equality of the first key in both tables. It then compares the next row in the second table to the first row in the first table. This is similar to nested loops, but because the inputs are sorted by the join key, it can stop as soon as it finds a row in the second table that is not matched and move to the next row in the first table. |
Large tables sorted by join key, where each table is a similar size |
| Adaptive Join |
The query starts by using a hash join but can change to nested loops during execution if the table that populates the hash table is small enough for nested loops to be more efficient. |
Queries where the input rows wildly swing and Batch Mode execution is used |
Compare Hash Join with Nested Loops and Merge Join by input shape and join-key ordering. section 10.5
