HIVE Query: How to select the 'join key' for "FULL OUTER JOIN"?

Question

I got the following error:

Multiple attributes named business_id in reportAttributeReferenceAmbiguous; p.business_id, s.business_id

from the SPARK/HIVE query:

SELECT
    business_id,
    p.p1,
    p.p2,
    s.s1,
    s.s2
FROM p_table p
FULL OUTER JOIN s_table s
    ON p.business_id = s.business_id

Since I am using FULL OUTER JOIN, business_id could be either p.business_id or s.business_id, so I didn't specify which one to use. (I thought some rows might have only p.business_id while some other rows might have only s.business_id, do I understand this wrong?)

So I am wondering what's the proper way to select business_id in the above query? Thanks!

result of join includes all columns from both tables – mangusta Nov 01 '20 at 03:59 — mangusta, Nov 01 '20 at 03:59
you might wanna use COALESCE(p.business_id, s.business_id) – eshirvana Nov 01 '20 at 04:08 — eshirvana, Nov 01 '20 at 04:08

score 2 · Accepted Answer · answered Nov 01 '20 at 09:15

2

Use

coalesce(p.business_id, s.business_id) as business_id

Or

nvl(p.business_id, s.business_id) as business_id

Or CASE statements like in this answer: https://stackoverflow.com/a/37744071/2700344

answered Nov 01 '20 at 09:15

leftjoin

36,950
8
57
116

score 0 · Answer 2 · answered Nov 01 '20 at 03:55

you have business_id in both table in your query you select business_id and didn't mention from which table.

SELECT
    business_id,
    p.p1,
    p.p2,
    s.s1,
    s.s2
FROM p_table p
FULL OUTER JOIN s_table s
    ON p.business_id = s.business_id

above query should change to

SELECT
    p.business_id AS pBI, s.business_id AS sBI,
    p.p1,
    p.p2,
    s.s1,
    s.s2
FROM p_table p
FULL OUTER JOIN s_table s
    ON p.business_id = s.business_id

I didn't check but you can try .

HIVE Query: How to select the 'join key' for "FULL OUTER JOIN"?

2 Answers2