I would like to share my experience in SQL joins.

The problem: I have 3 tables: 'vendors', 'invoices', 'terms'. I outer joined 'vendors' and 'invoices', now I'd like to join 'terms' table, but ON clause is conditional. If invoices.term_id is not null, than I join 'terms' table based on invoices.term_id, else I join 'terms' table based on vendors.default_terms_id. How can I use if in ON clause?

Solution:
I found it at Jef's SQL Server Blog, thanks him a lot.

1. Do not use conditions in ON clause.
2. Create two LEFT OUTER JOINs, based on each condition, i.e.

   LEFT OUTER JOIN terms t ON invoices.term_id = t.term_id
   LEFT OUTER JOIN terms t ON vendors.default_term_id = t.term_id

3. In SELECT statement with the help of COALESCE() function choose only that field, which is not null:

   SELECT COALESCE(invoices.term_id, vendors.default_term_id)

4. Into COALESCE() we put first that field, which is likely to be not null. But if it is null, than we use a default value, which is always not null.

5. Full query looks like this (there are additional constraints, but you'll get an idea):

   SELECT v.vendor_id AS "Vendor #",
       vendor_name AS "Vendor Name",
       vendor_contact_first_name AS "Contact First Name",
       vendor_contact_last_name AS "Contact Last Name",
       invoice_id AS "Invoice #",
       invoice_date AS "Invoice Date",
       COALESCE(t.terms_description, t_default.terms_description) AS "Description of Terms"
   FROM vendors v
       LEFT JOIN invoices i
           ON v.vendor_id = i.vendor_id
       LEFT JOIN terms t
           ON t.terms_id = i.terms_id 
       LEFT JOIN terms t_default
           ON t_default.terms_id = v.default_terms_id
   WHERE v.vendor_contact_last_name LIKE 'Z%'
     OR (v.vendor_contact_last_name > 'V' AND v.vendor_contact_last_name < 'Y')
   ORDER BY vendor_contact_last_name DESC;

6. Cheers!