Choice of Join hash join

Question

I believe sql server optimizer will choose loop join if one of the input table will be relatively small . like few rows. but in the following example show that it use hash join and using the smaller table as to build hash column in memory . so the query optimizer make a wrong choice ?

Answer 1

Hi,

Check the following articles if help.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15

https://www.mssqltips.com/sqlservertip/2115/understanding-sql-server-physical-joins/