Question 90689 in PostgreSQL Question posted in
The official documentation can be found here.

LEFT JOIN on column where value IS NOT NULL, otherwise join on another column – Postgresql

  • Joining 2 tables (orders, addresses)
  • Orders contains columns delivery_address_id (contains NULL values) and invoice_address_id (does NOT contain NULL values)
  • Addresses contains id column (does NOT contain NULL values)

Primarily, the LEFT JOIN must be performed on orders.delivery_address_id. However, in the case when its value is NULL in the row, perform LEFT JOIN on orders.invoice_address_id.

How do I deal with this?

I tried the operator OR but the result was not correct. I was also thinking about a CASE WHEN statement. My expectations are to get the LEFT JOIN working.

Tags:

2

Answers


  1. 0

    You can use the operator OR in the ON clause:

    SELECT ....
FROM Orders o LEFT JOIN Addresses a
ON a.id = o.delivery_address_id 
OR (o.delivery_address_id IS NULL AND a.id = o.invoice_address_id);

    Or, use COALESCE():

    SELECT ....
FROM Orders o LEFT JOIN Addresses a
ON a.id = COALESCE(o.delivery_address_id, o.invoice_address_id);
    Login or Signup to reply.
  2. 0

    So, you want to join on delivery_address_id, but sometimes it’s NULL, so you need invoice_address_id to be a fallback.

    These situations are where the COALESCE function really shines. COALESCE(delivery_address_id, invoice_address_id) will resolve to the delivery address ID if it isn’t NULL, otherwise it will resolve to the invoice address ID instead.

    Thus we can achieve the join you want:

        SELECT
        orders.some_field,
        addresses.address
      FROM
        orders
 LEFT JOIN
        addresses
        ON
        COALESCE(orders.delivery_address_id, orders.invoice_address_id) = addresses.id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search