sqlpostgresqlfull-outer-join

SQL full outer join, default value to column in other table


I'm attempting to do a full outer join in Postgres to see how data changes between the past 24 hours and the 24 hours before that ("today" and "yesterday" euphemistically). Some values only exist on one side of the join, in that case I'd like to use the value from the other side of the join.

Current result with below query:

Today Type Today Count Yesterday Type Yesterday Count
REPORT_A 2 REPORT_A 5
REPORT_B 4
REPORT_C 6

What I would like the output to be:

Today Type Today Count Yesterday Type Yesterday Count
REPORT_A 2 REPORT_A 5
REPORT_B 4 REPORT_B 0
REPORT_C 0 REPORT_C 6

For REPORT_B I'd like its values propagated to the right side and for REPORT_C I'd like its values propagated to the left side. How do I do this?

WITH
  yesterday_report_count AS (
    SELECT
      "reports"."report"."type" AS "type",
      "reports"."report"."status" AS "status",
      COUNT(*) AS "count"
    FROM
      "reports"."report"
    WHERE
      "reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
    GROUP BY
      "reports"."report"."type",
    ORDER BY
      "count" DESC,
      "reports"."report"."type" ASC,
  )


SELECT 
yesterday_report_count.type as "Today Type",
yesterday_report_count.count "Today Count",
today_report_count.type as "Yesterday Type",
today_report_count.count as "Yesterday Count"
FROM
  yesterday_report_count
FULL JOIN (
    SELECT
      "reports"."report"."type" AS "type",
      COUNT(*) AS "count"
    FROM
      "reports"."report"
    WHERE
    1=1
    AND "reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '48 HOURS' AND NOW()  - INTERVAL '24 HOURS'
    GROUP BY
      "reports"."report"."type",
    ORDER BY
      "count" DESC,
      "reports"."report"."type" ASC,
  ) AS today_report_count
  ON today_report_count.type = yesterday_report_count.type

I tried using coalesce() in the select statement but it seems that the "missing" values are not actually considered null.


Solution

  • Use COALESCE(). For example:

    select
      coalesce(t.a, u.a) as a,
      coalesce(t.b, 0) as tb,
      coalesce(t.a, u.a) as a, -- no need to repeat this, but if you must...
      coalesce(u.b, 0) as ub 
    from t
    full join u on u.a = t.a
    

    Result:

     a  tb   a  ub  
     -- ---- -- --- 
     A  100  A  120 
     B  200  B  0   
     C  0    C  300 
    

    See running example at db<>fiddle.