How are inner and left and right outer joins implemented in SQL Server?

EDIT (2020-03-11): The links in this 9+ year old answer are all invalid today. I would delete the answer, but SO won't let me since it was accepted back when it was actually useful.

Original Answer:

Here's some reading to get you started.