Merge two tables with criteria (query?)

Question

TABLES SCREENSHOT

Look at screenshot pls

I have two tables, I need to merge them into Table 3

Two tables:

• Table 1 (Primary) - ID with ALWAYS null "Fruits"
• Table 2 (Additional) - ID with ALWAYS "Fruits"

Some rules of Table 3 (result):

• show ID (Table 1) with null "Fruit" only if there is no same ID on Table 2
• show ID (Table 2) with "Fruit" only if there is same ID on Table 1
• dont show ID (Table 2) with Fruit if there is no same ID on Table 1

Help pls, I dont know how can I use query here... Or maybe smth else..

Thanks!

Answer 1

This should meet all your requirements:

={
FILTER(D3:E,MATCH(D3:D,A3:A,0)>0);
FILTER(A3:B,ISNA(MATCH(A3:A,D3:D,0)))
}

This filters out all rows that don't have keys in table 1, then concatenates that with the rows in table 1 that don't have instances in table 2.

The only downside is that this does not preserve any ordering, but you can sort by ID after this.