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.