I have three tables:
- Master Table A
| Key1 | Value1 |
|---|---|
| a | 1 |
| b | 2 |
| c | 3 |
| d | 4 |
| e | 5 |
| f | 6 |
- Table B
| Key1 | Value2 |
|---|---|
| b | x |
| c | y |
- Table C
| Key1 | Value2 |
|---|---|
| d | m |
| e | n |
Now I want to join A with B and C, in such a way that I get values from both B and C when the rows match but one row with Null if they do not match. Something like this:
| Key1 | Value1 | Value2 |
|---|---|---|
| a | 1 | NULL |
| b | 2 | x |
| c | 3 | y |
| d | 4 | m |
| e | 5 | n |
| f | 6 | NULL |
Now, one way to go halfway is to Left Join A with B and C, and then union the results, and then drop duplicates. However, this might result in the dropping of unwanted rows in a few cases too and is not optimal. What is the optimal way to achieve this?
CodePudding user response：
SELECT A.KEY1,A.VALUE1,X.VALUE2
FROM TABLEA AS A
LEFT JOIN
(
SELECT B.KEY1,B.VALUE2
FROM TABLEB AS B
UNION ALL
SELECT C.KEY1,C.VALUE2
FROM TABLEC AS C
)X ON A.KEY1=X.KEY1
If I understood your requirements correcly