Conditional SQL JOIN

#sql #join #condition

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.

Do not use conditions in ON clause.

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

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)
```
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.

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;