FULL OUTER JOIN emulation and aggregate function (e.g. SUM)

March 4, 2023

Let’s imagine I have two sqlite tables:

CREATE TABLE T_A (year_A INT, amount_A DOUBLE);

and

CREATE TABLE T_B (year_B INT, amount_B DOUBLE);

Those tables contain data below:

table T_A

  year_A   | amount_A
----------------------
    2020   |    100.0
    2020   |    200.0
    2021   |    300.0
    2021   |    400.0

table T_B

  year_B   | amount_B
----------------------
    2021   |   1000.0
    2021   |   2000.0
    2022   |   3000.0
    2022   |   4000.0

Now, what I would like is a VIEW with the SUM of amount_A and amount_B on every year present either in T_A or T_B with the result below:

table Sums

  year   | SUM(amount_A) | SUM(amount_B) 
------------------------------------------
  2020   |      300.0    |        0.0    
  2021   |      700.0    |     3000.0    
  2022   |        0.0    |     7000.0

If I use an INNER JOIN in my query, all I get (obviously) is a result for year 2021.
A FULL OUTER JOIN is certainly what I need. As it does not exist in sqlite, I tried to apply this workaround. But I cannot get it work properly as a SUM is involved too.

Thanks in advance for any help.

>Solution :

SQLite supports FULL OUTER JOIN since version 3.39.0.

First you must aggregate inside each of the tables and then do a FULL join on the aggregated results:

WITH 
  cte_A AS (SELECT year_A, SUM(amount_A) AS sum_A FROM T_A GROUP BY year_A),
  cte_B AS (SELECT year_B, SUM(amount_B) AS sum_B FROM T_B GROUP BY year_B)
SELECT COALESCE(a.year_A, b.year_B) year,
       COALESCE(a.sum_A, 0) AS sum_A,
       COALESCE(b.sum_B, 0) AS sum_B
FROM cte_A AS a FULL OUTER JOIN cte_B AS b
ON b.year_B = a.year_A
ORDER BY year;

For previous versions of SQLite use UNION ALL and then aggregate:

WITH cte AS (
  SELECT year_A AS year, amount_A, 0 AS amount_B FROM T_A
  UNION ALL
  SELECT year_B, 0, amount_B FROM T_B
)
SELECT year,
       SUM(amount_A) AS sum_A,
       SUM(amount_B) AS sum_B
FROM cte
GROUP BY year
ORDER BY year;

See the demo.