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?
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;