I'm trying to join two tables that need to match basically in three different columns but one of the columns might have nulls in both tables. The columns to join will be content_id, user_id and product_id but the products might or might not be the same in both tables. Let's see an example:

Table A

Table B

I need my final table to look like

I tried to use full outer join USING(content_id, user_id, product_id) but doesn't really work.

CodePudding user response：

Seems like you are looking for full join :

select
    coalesce(t1.Content_id, t2.Content_id) Content_id,
    coalesce(t1.User_ID, t2.User_ID) User_ID,
    coalesce(t1.Product_ID, t2.Product_ID) Product_ID,
    t1.Saved,
    t1.Clicked,
    t2.Liked,
    t2.Shared
from table1 t1 
full outer join table2 t2 
    on t1.Content_id = t2.Content_id
    and t1.User_ID = t2.User_ID
    and t1.Product_ID = t2.Product_ID

db<>fiddle here

CodePudding user response：

Here's another way to do this.

SELECT a.*, b.liked, b.shared
FROM a NATURAL LEFT JOIN b
UNION
SELECT b.content_id, b.user_id, b.product_id, a.clicked, a.saved, b.liked, b.shared
FROM a NATURAL RIGHT JOIN b;

We have to enumerate the columns in the second part, because it will naturally select all of b first, and UNION doesn't line up corresponding columns.

Postgres doesn't accept NATURAL FULL JOIN. Not sure why.