Merge two tables with same columns and get the column value in specific table-CodePudding

I have two tables that have the same column of position and salary.

Table_a

ID	Name	Position	Salary
1	Peter	Sale	10,000
2	Tom	IT	15,000
3	Jane	Sale	12,000
4	Mary	PR	8,000
5	John	IT	14,000

Table_b

ID	Position	Salary
1	Driver	9,000
2	Manager	20,000
4	Sale	13,000

I would like to merge two tables that the output is based on the Position value and Salary value of Table_b.

Output

ID	Name	Position	Salary
1	Peter	Driver	9,000
2	Tom	Manager	20,000
3	Jane	Sale	12,000
4	Mary	Sale	13,000
5	John	IT	14,000

Please give me an advice how can I query and get the above output.

CodePudding user response：

SELECT id, 
       t1.name, 
       COALESCE(t2.position, t1.position), 
       COALESCE(t2.salary, t1.salary)
FROM table_a t1
LEFT JOIN table_b t2 USING (id)

CodePudding user response：

Some rows in table_a don't have a matching row in table_b. Any rows in table_b that don't have a matching row in table_a will be ignored. We assume that ID is unique in each table (meaning for example there won't be two rows in table_b with the same ID value). We will match the rows on ID value.

We can make table_a the driving table in an outer join to table_b. Let's first get that working, returning all the columns from both tables, and verify the return is what we expect.

SELECT a.ID
     , a.Name
     , a.Position
     , a.Salary
     , b.ID            AS b_ID
     , b.Position      AS b_position
     , b.Salary        AS b_salary
  FROM table_a a
  LEFT
  JOIN table_b b
    ON b.ID = a.ID
 ORDER
    BY a.ID

We see that in the result from the first query, on rows where we didn't get a matching row from table_b, column b_ID (and all the columns from table_b) are NULL.

We can add some expressions in the SELECT list to get the Salary from table_b where we found a matching row, or otherwise return Salary from table_a where we didn't.

There's lots of possible expressions to do that, but they all key on the idea that when we don't have a matching row from the outer joined table, the values in those columns will be NULL.

So the trick is test whether we found a matching row from table_b (or if we did get a matching row in table_b, the row had a NULL value for Salary)

Adding some example expressions to check if b.ID or b.Salary is NULL.

SELECT a.Name
     , a.Position
     , a.Salary
     , b.ID            AS b_ID
     , b.Position      AS b_position
     , b.Salary        AS b_salary

     , CASE WHEN b.ID IS NULL THEN a.Salary ELSE b.Salary END AS _Salary_x1
     , IFNULL(b.Salary,a.Salary)              AS _Salary_x2 
     , IF(b.Salary IS NULL,a.Salary,b.Salary) AS _Salary_x3 

  FROM table_a a
  LEFT
  JOIN table_b b
    ON b.ID = a.ID
 ORDER
    BY a.ID