loading...

Conditional SQL JOIN

tyzia profile image Andrei Navumau ・2 min read

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!

Discussion

pic
Editor guide
Collapse
gperja profile image
Genti Perja

Easier Way:
LEFT JOIN terms t ON t.terms_id = ISNULL(i.terms_id, v.default_terms_id)

Cheers :)