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:

SELECT

 o.FirmCode,

 o.StreetAddress,

 o2.MailingAddress

FROM

 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.