CraigFr has a great series of posts in his blog describing the difference between the various logical and physical join types, the details of how SQL Server implements these joins, and the things that the query optimizer takes into account when selecting...

Sometime we get complaints that a query is slower than it could be because a filter isn’t pushed very deeply down into a plan. For example, consider this hypothetical poor performance scenario (my apologies in advance for the lack of normalization): ...