How to join two total tables using sql?-CodePudding

For a university work we have two tables in sql:

table1:

         column_name1      number_P1
           PARIS             10
           LISBOA            20
           RIO               30

table2:

         column_name2      number_P2
           PARIS             100
           NEW YORK          300

I need to join the two tables by adding the total number of people in each city. So I tried to do:

SELECT table1.column_name1,
       number_P2   number_P1 AS TOTAL       
       
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

However, if a city A appears in table 1 and does not appear in table 2 this would not work. The same would happen if a City B appears in table 2 and does not appear in table 1. How can I generalize these situations?

Desired output:

         column_name      number_P
           PARIS             110
           LISBOA            20
           RIO               30
           NEW YORK          300

CodePudding user response：

We can try to use UNION ALL with SUM instead of JOIN

SELECT column_name, 
       SUM(number_P) number_P
FROM (
    SELECT column_name1 as column_name,number_P1 as number_P
    FROM table1 
    UNION ALL
    SELECT column_name2,number_P2
    FROM table2
) t1
GROUP BY column_name

CodePudding user response：

Another way to achieve this without a subquery.

SELECT IFNULL(table1.column_name1,table2.column_name2) AS ColumnName,
       (IFNULL(number_P2,0)  IFNULL(number_P1,0)) AS TOTAL       
FROM table1
FULL JOIN table2 ON table1.column_name1 = table2.column_name2;

Output

ColumnName	TOTAL
PARIS	110
LISBOA	20
RIO	30
NEW YORK	300

To replace 'RIO' with 'RIO DE JANEIRO'

SELECT CASE IFNULL(table1.column_name1,table2.column_name2) 
    WHEN 'RIO' THEN 'RIO DE JANEIRO' 
    ELSE IFNULL(table1.column_name1,table2.column_name2) END AS ColumnName,
    (IFNULL(number_P2,0)  IFNULL(number_P1,0)) AS TOTAL       
FROM table1
FULL JOIN table2 ON table1.column_name1 = table2.column_name2;