Merge tables with PostgreSQL

问题:

The title of this question is not accurate but I didn't know how to summarize it. Please feel free to write it again if you can!

Here is an extract of two tables:

Table table_a

code  | year   | nb_a
------+--------+------
  A1  |   2017 |    1      
  A2  |   2012 |    2
  A3  |   2014 |    2

Table table_b

code  | year   | nb_b
------+--------+------
  A1  |   2013 |    1
  A1  |   2014 |    1
  A2  |   2012 |    1

I need to merge these tables in order to get this output:

code  | year   | nb_a | nb_b | total
------+--------+------+------+-------
  A1  |   2013 |    0 |    1 |     1
  A1  |   2014 |    0 |    1 |     1
  A1  |   2017 |    1 |    0 |     1
  A2  |   2012 |    2 |    1 |     3
  A3  |   2014 |    2 |    0 |     2

I can't find the correct query. I would need something like below (I know it doesn't do the job) but how to get all codes and years merged in one table as codes and years are not all repeated in both tables...

SELECT 
  code,
  "year",
  table_a.nb_a,
  table_b.nb_b,
  table_a.nb_a + table_b.nb_b AS total

FROM table_a, table_b
WHERE table_a.code = table_b.code;

---

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

CREATE TABLE public.table_a (code TEXT, "year" INTEGER, nb_a INTEGER);
CREATE TABLE public.table_b (code TEXT, "year" INTEGER, nb_b INTEGER);

INSERT INTO public.table_a (code, "year", nb_a) VALUES (A1, 2017, 1), (A2, 2012, 2), (A3, 2014, 2);
INSERT INTO public.table_b (code, "year", nb_b) VALUES (A1, 2013, 1), (A1, 2014, 1), (A2, 2012, 1);

回答1:

yu probably are looking for FULL OUTER JOIN

SELECT
  coalesce(a.code,b.code),
  coalesce(a."year",b.year),
  coalesce(a.nb_a,0),
  coalesce(b.nb_b,0),
  coalesce(a.nb_a,0) + coalesce(b.nb_b,0) AS total
FROM table_a a full outer join table_b b on a.code = b.code and a.year = b.year;
 coalesce | coalesce | coalesce | coalesce | total
----------+----------+----------+----------+-------
        1 |     2013 |        0 |        1 |     1
        1 |     2014 |        0 |        1 |     1
        1 |     2017 |        1 |        0 |     1
        2 |     2012 |        2 |        1 |     3
        3 |     2014 |        2 |        0 |     2
(5 rows)