I have two tables (Table 1 and Table 2 in the following example) with two columns each. What I am trying to do is:
- keep all the entries of Table 1
- add the entries from Table 2 if the element of col_3 (in Table 2) is also an element of col_1 (in Table 1)
- the Result Table will have three columns: the two original ones from Table 1, and col_4 from Table 2
Table 1
| col_1 | col_2 |
|---|---|
| 1 | a |
| 1 | b |
| 2 | c |
| 3 | d |
Table 2
| col_3 | col_4 |
|---|---|
| 1 | w |
| 1 | x |
| 2 | y |
| 4 | z |
Result Table
| col_1 | col_2 | col_4 |
|---|---|---|
| 1 | a | NULL |
| 1 | b | NULL |
| 2 | c | NULL |
| 3 | d | NULL |
| NULL | NULL | w |
| NULL | NULL | x |
| NULL | NULL | y |
In the example, all the elements of Table 1 are in the Result Table (and populate col_1 and col_2), while only the first three elements of Table 2 are in the Result Table (and populate col_4).
This looks very similar to a left join in some sense. But instead of just creating a new column and adding the values to it, how do I create the new rows and fill them with the entries from Table 2? Maybe some combination of union and join might work.
CodePudding user response：
You can solve this with a UNION:
SELECT col_1, col_2, NULL as col_3 FROM table1
UNION
SELECT NULL, NULL, col_3 FROM table2
WHERE col_3 in (SELECT DISTINCT col_1 FROM table1)
Despite the initial reaction to use a join, you are actually stacking these tables/result-sets on top of each other, therefore a UNION is more appropriate.
CodePudding user response：
I'd say a UNION ALL is more appropriate as it avoids the performance penalty of checking and removing duplicates. There should be no duplicates because you're merging 3 distinct attributes from 3 different tables.