How can I join two results-CodePudding

I have 3 tables purchase, air_transfers and sea_transfers. I have made the example code in

CodePudding user response：

You can do

SELECT COALESCE(st.purchase_id, at.purchase_id) as purchase_id

Fiddle

CodePudding user response：

I think you are looking for UNOIN ALL instead of OUTER FULL JOIN, because UNION ALL will combine two results air_transfers and sea_transfers tables, you will get all purchase_id and units values from two tables.

SELECT  purchase_id, SUM(units) AS units
FROM (
   SELECT purchase_id,units FROM air_transfers
   UNION ALL
   SELECT purchase_id,units FROM sea_transfers
) t1
GROUP BY purchase_id