The query engine in SQL Server is quite versatile, allowing join expressions that are far beyond the basic "ON a.Field = b.Field" construct. Example:

I have an Office table with an OfficeID and a BillingOfficeID. Normally the office statements go to the mailing address, except if there is a BillingOfficeID then the statements need to go there instead. A brief code snippet might be as follows:

  Office o
  INNER JOIN Office o2
    ON COALESCE(o.BillingOfficeID,o.OfficeID)
      = o2.OfficeID

T-SQL allows the join using an expression, in this case COALESCE() which returns the first non-NULL value in the list. Powerful stuff.