I would like to educate myself on what goes on under the hood in SQL Server, so I'm about to delve into Grant Fritchey's book Dissecting SQL Server Execution Plans. As it's 181 pages I just wanted to ask this simple question, which is what first got me interested in this in the first place - hopefully it will pique my interest (plus I'm too impatient to wait until I've waded through this tome to get some kind of answer!)
I am using this miniature version of Northwind to run this simple query:
SELECT Orders.OrderID ,Orders.OrderDate ,[Order Details].UnitPrice FROM Orders JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
Which gives the following execution plan:
Which seems sensible, given both tables have indexing / sorting on the joining fields. But if I add another table (products) like so:
SELECT Orders.OrderID ,Orders.OrderDate ,[Order Details].UnitPrice ,Products.ProductName FROM Orders JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID JOIN Products ON Products.ProductID = [Order Details].ProductID
Suddenly I have Hash matches. This seemed odd to me (remember I'm just beginning to learn this stuff!), as I thought hash matches were for large, unsorted joins. Why would SQL think one join type is okay in the first query and not the second, even though surely they are joining the same number of rows / same indexes etc between Orders and Order Details in both queries?
Orders has 830 rows, Order Details 2155 and Products 77.
OrderID has a clustered indexes on Orders and Order Details, and ProductID has a non-clustered index on Order Details and clustered on Products.