What algorithms does SQL use?

Joins are performed in three methods: 1. Nested Loop 2. Hash 3. Merge

Get more about these join methods here:

http://www.sqlservercentral.com/articles/Advanced+Querying/optimizerjoinmethods/1708/

I would suggest you get a copy of SQL Server 2008 Internals by Delaney, Randal, Tripp and more. Excellent book on the internal workings of SQL Server.

http://www.amazon.com/Microsoft-SQL-Server-2008-Internals/dp/0735626243/ref=sr_1_1?s=books&ie=UTF8&qid=1289565465&sr=1-1

You can take a look at the Microsoft blog about SQL Server Query Processing, which discusses some SQL Server internals. (The originally linked Craig Freedman blog has been moved there.) Just browse for the interesting topics. If I understood it right, he also wrote some chapter for the already mentioned book series "SQL Server Internals".

You can search the above linked site for the following topics to shed some light on how the different JOIN algorithms work internally:

• Nested Loop Join
• Hash Join
• Merge Join