How do I concatenate outer joins on multiple tables in postgres?

Question

I want to return all orders from the database between two given dates. I want to return all orders even if the orders do not have specified values from joined tables (contacts, addresses, etc).

When I run query 1 I see I have 39 orders as a result. But when I run query 2 with my joins I only return 31 orders. I checked the database and it's because 8 orders do not have records on the joined tables.

But I thought a RIGHT OUTER JOIN would return all values from the join, even if there is a NULL value in the joined table?

Query 1

SELECT * FROM orders 
WHERE orders.created_at > '2020-02-01' AND orders.created_at < '2020-02-02'

Query 2

SELECT orders.id, orders.status, orders.created_at AS "Date-Time", orders.inbound_ticker, orders.inbound_sum, orders.outbound_ticker, orders.outbound_sum, orders.outbound_type,  
    CASE WHEN orders.inbound_type='Cryptocurrency' OR orders.outbound_type='Cryptocurrency' THEN 'Crypto' ELSE 'Fiat' END AS "tx type",
    customers.first_name AS "Customer First Name", customers.last_name AS "Customer Last Name", addresses.street, addresses.city, addresses.state, addresses.country, addresses.postal_code
    FROM orders
RIGHT OUTER JOIN customers ON orders.customer_id = customers.id
RIGHT OUTER JOIN contacts ON contacts.contactable_id = customers.id
RIGHT OUTER JOIN addresses ON customers.id = addresses.locatable_id
WHERE orders.created_at >= '2020-2-01' AND orders.created_at < '2020-2-02'
ORDER BY orders.created_at ASC;

Gordon Linoff · Accepted Answer · 2020-02-18 19:15:26Z

1

If you want all orders, start with orders in the FROM clause and use LEFT JOIN:

FROM orders LEFT JOIN
     customers
     ON orders.customer_id = customers.id LEFT JOIN
     contacts
     ON contacts.contactable_id = customers.id LEFT JOIN
     addresses
     ON customers.id = addresses.locatable_id
WHERE orders.created_at >= '2020-02-01' AND
      orders.created_at < '2020-02-02'

answered Feb 18, 2020 at 19:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I concatenate outer joins on multiple tables in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related