How do I fill the null values in Table A using values from Table B while keeping all other columns/rows intact?
Table A
| name | dept | job |
|---|---|---|
| jon | null | analyst |
| mary | null | supervisor |
| lucy | null | actuary |
| mark | retail | manager |
| cindy | retail | sales |
Table B
| name | dept | job |
|---|---|---|
| jon | hr | null |
| mary | hr | null |
| lucy | finance | null |
attempts to use joins has incorrect results due to having to select which columns show in final table. ex:
SELECT a.name, b.dept, a.job
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.name=b.name
will show
| name | dept | job |
|---|---|---|
| jon | hr | analyst |
| mary | hr | supervisor |
| lucy | finance | actuary |
| mark | null | manager |
| cindy | null | sales |
I've tried out different types of joins, but can't seem to figure it out. Thanks in advance!
CodePudding user response：
Use COALESCE() to combine two values that could be null.
For example:
SELECT a.name,
coalesce(a.dept, b.dept) as dept,
coalesce(a.job, b.job) as job
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.name=b.name