DEV Community

Andrei Navumau
Andrei Navumau

Posted on

2

Conditional SQL JOIN

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!

Top comments (1)

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 :)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more