FULL OUTER JOIN to merge tables with PostgreSQL

Question

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 mentionned above.

Here are my datasets:

-- Table 'brcht' (empty)

insee  | annee  | nb
-------+--------+-----


-- Table 'cana'

insee  | annee  | nb
-------+--------+-----
036223 |   2017 |   1
086001 |   2016 |   2


-- Table 'font' (empty)

insee  | annee  | nb
-------+--------+-----


-- Table 'nr'

insee  | annee  | nb
-------+--------+-----
036223 |   2013 |   1
036223 |   2014 |   1
086001 |   2013 |   1
086001 |   2014 |   2
086001 |   2015 |   4
086001 |   2016 |   2

Here is the query:

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

ORDER BY COALESCE(brcht.insee, cana.insee, font.insee, nr.insee), COALESCE(brcht.annee, cana.annee, font.annee, nr.annee);

In the result, I still have two rows instead of one for insee='086001' (see below). I need to get one row per insee and in this example, the two 2 values should be on the same line with a total column showing a 4 value.

Thanks again for help!

Here are the SQL scripts to create easily the above tables:

CREATE TABLE public.brcht (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER);
CREATE TABLE public.cana (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER);
CREATE TABLE public.font (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER);
CREATE TABLE public.nr (insee CHARACTER VARYING(10), annee INTEGER, nb INTEGER);

INSERT INTO public.cana (insee, annee, nb) VALUES ('036223', 2017, 1), ('086001', 2016, 2);
INSERT INTO public.nr(insee, annee, nb) VALUES ('036223', 2013, 1), ('036223', 2014, 1), ('086001', 2013, 1), ('086001', 2014, 2), ('086001', 2015, 4), ('086001', 2016, 2);

Dan Getz · Accepted Answer · 2017-06-15 11:15:09Z

2

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)

answered Jun 15, 2017 at 11:15

Dan Getz

18.3k2 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wiltomap Over a year ago

Very clear, thanks! Didn't know about USING statement for joins.

Paul Maxwell · Accepted Answer · 2017-06-15 10:11:02Z

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

Vao Tsun · Accepted Answer · 2017-06-15 11:22:18Z

0

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

edited Jun 15, 2017 at 11:22

answered Jun 15, 2017 at 10:10

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

4 Comments

wiltomap Over a year ago

Why would you join nr against cana? I don't understand the way to join the 4 tables... In my example, I first joined brcht against cana, then cana against font, then font against nr. It seemed logical to me to proceed like this. Is there a logical way of joining the tables together?

Vao Tsun Over a year ago

@wiltomap tried to explain. mind that if you dont use () joins happen left to right, so last join joins whole previous set on NULL column - and you get all from (brcht,cana,font) AND all from nr (all - because they have no common values on columns you use to join). Hopefully it makes sense - explaining is not my best skill

wiltomap Over a year ago

OK I did understand, thank you! The thing is that the content of the 4 tables is going to change periodically so I can't keep adapting the joins depending on this... I need a way to join the tables together that will fit any tables contents.

Vao Tsun Over a year ago

then use parenthesis to nest join so each next join would be on "coalesced" value

Collectives™ on Stack Overflow

FULL OUTER JOIN to merge tables with PostgreSQL

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related