Replace JOIN OR-CodePudding

How can i modify join to get rid of JOIN AND (... OR ...) conditions?

select
table1.id,
table1.country,
table1.city,
table1.platform,
sum(table2.installs) as installs
from 
  schema.table1  
  inner join schema.table2 on table1.id = table2.id 
  and (
    table1.country = table2.country 
    or table1.country is null
  ) 
  and (
    table1.city = table2.utm_source 
    or table1.utm_source is null
  ) 
  and (
    table1.platform = table2.platform  
    or table1.platform is null
  ) 
group by 
table1.id,
table1.country,
table1.city,
table1.platform,

These conditions: OR table1.platform IS NULL

CodePudding user response：

You can use coalesce() and the row type to shorten your query:

select table1.id, table1.country, table1.city, table1.platform,
       sum(table2.installs) as installs
  from schema.table1  
       join schema.table2 
         on (table1.id, 
             coalesce(table1.country, table2.country),
             coalesce(table1.city, table2.city),
             coalesce(table1.platform, table2.platform)
            ) = (table2.id, table2.country, table2.city, table2.platform)
 group by table1.id, table1.country, table1.city, table1.platform
;

If you want to match the condition when country, city, or platform are null in both tables, then change the = to is not distinct from.

CodePudding user response：

select
table1.id,
table1.country,
table1.city,
table1.platform,
(SELECT sum(table2.installs) from schema.table2 on table1.id = table2.id 
  and (
    table1.country = table2.country 
    or table1.country is null
  ) 
  and (
    table1.city = table2.utm_source 
    or table1.utm_source is null
  ) 
  and (
    table1.platform = table2.platform  
    or table1.platform is null
  ) 
)as installs
from 
  schema.table1

I don't know it's work or not but it's fine to try subquery on select to sum table2.installs