case expression inside this left join?-CodePudding

so I have this left join

LEFT JOIN LATERAL (SELECT d.country FROM db.patient_info d
           WHERE  d.id IN (SELECT DISTINCT st.category FROM db.surgery_types st, db.surgery_record sr
               WHERE sr.id = st.surgery_record_id AND sr.surgery_type_id = m.id)
           ORDER BY d.priority, d.country
           LIMIT 1
           
         ) c ON TRUE

the issue is that sometimes d.country comes back null. How can I add a case statement in the left join so that when d.country IS NULL then 'USA'?

My results look like this

Patient Name	Surgery Type
Dave	USA
Richard	EU
Ben	EU
Sally	JP
Bob	null
Dicky	null

I want to modify the left join so that it looks more like this

Patient Name	Surgery Type
Dave	USA
Richard	EU
Ben	EU
Sally	JP
Bob	USA
Dicky	USA

Thoughts?

CodePudding user response：

Use coalesce which returns the first non-null value.

-- I have no idea if this lateral join is valid.
LEFT JOIN LATERAL (
  SELECT coalesce(d.country, 'USA')
  FROM db.patient_info d
  WHERE  d.id IN (
    SELECT DISTINCT st.category
    FROM db.surgery_types st, db.surgery_record sr
    WHERE sr.id = st.surgery_record_id AND sr.surgery_type_id = m.id
  )
  ORDER BY d.priority, d.country
  LIMIT 1         
) c ON TRUE

Though the order by will still use null so it might not sort properly. You might want to split this into a CTE.

-- Again, no idea if the lateral join is valid,
-- just showing a technique.
with countries as(
  SELECT coalesce(d.country, 'USA') as country
  FROM db.patient_info d
  WHERE  d.id IN (
    SELECT DISTINCT st.category
    FROM db.surgery_types st
    JOIN db.surgery_record sr ON sr.id = st.surgery_record_id
    -- Don't know what m is
    WHERE sr.surgery_type_id = m.id
  )
)
with first_country as (
  select country
  from countries
  order by priority, country
  limit 1
)
select
...
LEFT JOIN LATERAL countries on true

Finally, it might be simpler and faster to update the table to set all null countries to USA, and then make the column not null.

CodePudding user response：

Not looking into your business logic and whether a lateral join is needed at all or a scalar subquery in the select list of expressions would be enough, here is my suggestion.

CROSS JOIN LATERAL 
(
 select coalesce
 (
   ( /* your lateral subquery in the brackets here */),
   'USA'
 ) as country
) as c

You do not need left join anymore. Please note that this will only work if the subquery is scalar.