how to combine two rows with different amount of rows

[luo]

I tried to use union and join but it will cause chaos. and combine two rows together.

For Example

Input 1:

Input 2:

Output:

I didn't find a tool that can help me combine two rows correspondingly. Thanks guys. 

[RolandSchubert · Accepted solution]

Hi @luo ,

if I got you right, you need both Join and Union. First you join the rows of table 1 and table 2 that match, then you union the matched rows with the one that don't match.
I've attached a sample.

Let me know if it works for you.

Best,

Roland