FULL OUTER JOIN To Merge Tables With PostgreSQL

Following this post, I still have an issue when I apply the answer given by @Vao Tsun to a bigger dataset made this time of 4 tables instead of 2 tables in the related post mention

Solution 1:

Inspired by other answers, but perhaps better organized:

SELECT *, 
       brcht + cana + font + nr AS total 
FROM   (SELECT insee, 
               annee, 
               SUM(Coalesce(brcht.nb, 0)) brcht, 
               SUM(Coalesce(cana.nb, 0))  cana, 
               SUM(Coalesce(font.nb, 0))  font, 
               SUM(Coalesce(nr.nb, 0))    nr 
        FROM   brcht 
               full outer join cana USING (insee, annee) 
               full outer join font USING (insee, annee) 
               full outer join nr USING (insee, annee) 
        GROUP  BY insee, 
                  annee) t 
ORDER  BY insee, 
          annee; 

Giving:

 insee  | annee | brcht | cana | font | nr | total 
--------+-------+-------+------+------+----+-------
 036223 |  2013 |     0 |    0 |    0 |  1 |     1
 036223 |  2014 |     0 |    0 |    0 |  1 |     1
 036223 |  2017 |     0 |    1 |    0 |  0 |     1
 086001 |  2013 |     0 |    0 |    0 |  1 |     1
 086001 |  2014 |     0 |    0 |    0 |  2 |     2
 086001 |  2015 |     0 |    0 |    0 |  4 |     4
 086001 |  2016 |     0 |    2 |    0 |  2 |     4
(7 rows)

Solution 2:

You will need to perform an GROUP BY and SUM() the bigint columns, over the query you are now using.

select
    insee, annee
    , sum(brcht) brcht
    , sum(cana) cana
    , sum(font) font
    , sum(nr) nr
    , sum(total) total
from (
    SELECT
     COALESCE(brcht.insee, cana.insee, font.insee, nr.insee) AS insee,
     COALESCE(brcht.annee, cana.annee, font.annee, nr.annee) AS annee,
     COALESCE(brcht.nb,0) AS brcht,  
     COALESCE(cana.nb,0) AS cana,
     COALESCE(font.nb,0) AS font,
     COALESCE(nr.nb,0) AS nr,
     COALESCE(brcht.nb,0) + COALESCE(cana.nb,0) + COALESCE(font.nb,0) + COALESCE(nr.nb,0) AS total

    FROM public.brcht
      FULL OUTER JOIN public.cana ON brcht.insee = cana.insee AND brcht.annee = cana.annee
      FULL OUTER JOIN public.font ON cana.insee = font.insee AND cana.annee = font.annee
      FULL OUTER JOIN public.nr   ON font.insee = nr.insee AND font.annee = nr.annee
      ) d
group by
    insee, annee

Solution 3:

try:

t=# SELECT
 COALESCE(brcht.insee, cana.insee, font.insee, nr.insee) AS insee,
 COALESCE(brcht.annee, cana.annee, font.annee, nr.annee) AS annee,
 COALESCE(brcht.nb,0) AS brcht,
 COALESCE(cana.nb,0) AS cana,
 COALESCE(font.nb,0) AS font,
 COALESCE(nr.nb,0) AS nr,
 COALESCE(brcht.nb,0) + COALESCE(cana.nb,0) + COALESCE(font.nb,0) + COALESCE(nr.nb,0) AS total
FROM public.brcht
  FULL OUTER JOIN public.cana ON brcht.insee = cana.insee AND brcht.annee = cana.annee
  FULL OUTER JOIN public.font ON cana.insee = font.insee AND cana.annee = font.annee
  FULL OUTER JOIN public.nr   ON cana.insee = nr.insee AND cana.annee = nr.annee
ORDER BY COALESCE(brcht.insee, cana.insee, font.insee, nr.insee), COALESCE(brcht.annee, cana.annee, font.annee, nr.annee);
 insee  | annee | brcht | cana | font | nr | total
--------+-------+-------+------+------+----+-------
 036223 |  2013 |     0 |    0 |    0 |  1 |     1
 036223 |  2014 |     0 |    0 |    0 |  1 |     1
 036223 |  2017 |     0 |    1 |    0 |  0 |     1
 086001 |  2013 |     0 |    0 |    0 |  1 |     1
 086001 |  2014 |     0 |    0 |    0 |  2 |     2
 086001 |  2015 |     0 |    0 |    0 |  4 |     4
 086001 |  2016 |     0 |    2 |    0 |  2 |     4
(7 rows)

In your example you join nr against font, while you probably want to join it against cana?..

Also please check out here: https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-JOIN

In the absence of parentheses, JOIN clauses nest left-to-right

update

Explaining logic: try select * from public.brcht, adding other table one, by one column from "righter" tables appear, so when you run all four joined, you get:

t=# select * 
FROM public.brcht 
FULL OUTER JOIN public.cana ON brcht.insee = cana.insee AND brcht.annee = cana.annee
FULL OUTER JOIN public.font ON cana.insee = font.insee AND cana.annee = font.annee
FULL OUTER JOIN public.nr   ON font.insee = nr.insee AND font.annee = nr.annee
t-# ;
 insee | annee | nb | insee  | annee | nb | insee | annee | nb | insee  | annee | nb
-------+-------+----+--------+-------+----+-------+-------+----+--------+-------+----
       |       |    | 036223 |  2017 |  1 |       |       |    |        |       |
       |       |    | 086001 |  2016 |  2 |       |       |    |        |       |
       |       |    |        |       |    |       |       |    | 036223 |  2013 |  1
       |       |    |        |       |    |       |       |    | 036223 |  2014 |  1
       |       |    |        |       |    |       |       |    | 086001 |  2013 |  1
       |       |    |        |       |    |       |       |    | 086001 |  2014 |  2
       |       |    |        |       |    |       |       |    | 086001 |  2015 |  4
       |       |    |        |       |    |       |       |    | 086001 |  2016 |  2
(8 rows)

so the 8th column is font.annee (mind - it is null everywhere) - you join it with nr.insee - no matches - so full join takes ALL rows from previous three tables joined and ALL rows from nr table - and you get 8 rows