Search code examples
sqlperformance

Join on two columns, if null then only join on one

I have the following two tables:

customers:

customer_id department_id
aaaa 1234
bbbb 3456

status:

department_id customer_id status
1234 NULL silver
3456 bbbb gold
1234 bbbb gold

I want to join status on customers, but if if it returns NULL I want to give the customer the department default. My ideal Output for this would be the following:

customer_id department_id status
aaaa 1234 silver
bbbb 3456 gold

I have tried to do two left joins, but am getting a memory usage error. Is there another way to do this that is efficient?

Solution

  • You can do:

    select c.*, coalesce(s.status, d.status) as status
from customers c
left join status d on d.department_id = c.department_id 
                  and d.customer_id is null
left join status s on s.department_id = c.department_id 
                  and s.customer_id = c.customer_id