When is it best to use JOIN or UNION?

Question

Suppose I have three tables:

Primaries:

Dependents:

Transactions:

I am just wondering, what is the SQL operation that I have to use in order for me to get the passportId for each borrower? Should I use JOIN or UNION? And what would be the difference for the both of them given my schema above?

Output Expected:

Answer 1

UNION is combining 2 tables that have the same columns into one it will add one table under the other (will remove duplicate records) union all will leave the duplication

Join will add columns from 2 different columns tables by a specific logic

in you case you cannot use Union as None of the tables are the same you need to use Join

select t.*, p.passportid, p.passportid , p.name
from transactions t left join
     primaries p
     on t.borroweruuid = p.uuid

Answer 2

If I understand correctly, you want left joins and coalesce():

select t.*, coalesce(p.passportid, dp.passportid) as passportid
from transactions t left join
     primaries p
     on t.borroweruuid = p.uuid left join
     dependents d
     on t.borroweruuid = d.uuid left join
     primaries pd
     on pd.id = d.primaryid;

The first left join matches directly to the primaries table. The second matches through the dependents.