The query engine in SQL Server is quite versatile, allowing join
expressions that are far beyond the basic "ON a.Field = b.Field"
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:
INNER JOIN Office o2
T-SQL allows the join using an expression, in this case COALESCE() which returns the first non-NULL value in the list. Powerful stuff.