title: Does HashMatch join use temp tables for both datasets in SQL Server?
body: <p>I am executing the following query in AdventureWorks2014 DB.</p>
  
  <pre><code>SET STATISTICS IO ON
  
  SELECT 
      e.JobTitle,
      a.City,
      p.LastName + ', ' + p.FirstName AS EmployeeName
  FROM 
      HumanResources.Employee AS e
  JOIN 
      Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID
  JOIN 
      Person.Address a ON bea.AddressID = a.AddressID
  JOIN 
      Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;
  
  SET STATISTICS IO OFF
  </code></pre>
  
  <p>It generates an execution plan which contains a Hash Match (Inner Join) of an Output of an Nested Join (say output 1, 290 rows) and an output of Index Scan of Address table (say output 2, 19614 rows).</p>
  
  <p>When a Hash Match (Inner Join) Occurs, does it create Temp Tables (#tables) for both Output 1 and Output 2 or just Output 1 ?</p>
  
  <p>If the answer is just 'Output 1', then why doesn't it creates a data structure 'In Memory' for the smaller output instead of creating a physical tables which requires file storage ?</p>