CODEWITHSUNDEEP
sqljoinhivehiveql
Noman Shaukat
Noman Shaukat

Reputation: 11

I am having hard time writing a HiveQL Join

I am pretty sure this question have been asked but can't get my search query to return the answer. I have two table 

**Table Online**
Col1   Col2    Score   |
a      b       1       |
a      c       2       |
a      d       3       |
f      e       4       |

**Table Offline**
Col1   Col2    Score   |
a      m       10      |
a      c       20      |
a      d       30      |
t      k       40      |


**Table Output**
Col1  Col2  Online.Score  Offline.Score    |
a     c     2             20               |    
a     d     3             30               |
a     b     1                              |
a     m                   10               |

Upvotes: 1

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can do this with a full join:

select coalesce(onl.col1, ofl.col1) as col1,
       coalesce(onl.col2, ofl.col2) as col2,
       onl.score, ofl.score
from (select onl.*
      from online onl
      where onl.col1 = 'a'
     ) onl full join
     (select ofl.*
      from offline ofl
      where ofl.col1 = 'a'
     ) ofl
     on onl.col1 = ofl.col1 and onl.col2 = ofl.col2;

Filtering is tricky withe full join, which is why this uses a subquery.

Upvotes: 2

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Use below query!

SELECT online.col1
    ,online.col2
    ,coalesce(online.score, 0) AS onlinescore
    ,coalesce(offlilne.score, 0) AS offlinescore
FROM online
INNER JOIN offline
    ON online.col1 = offline.col1
    AND online.col2 = offline.col2
UNION ALL
SELECT online.col1
    ,online.col2
    ,coalesce(online.score, 0) AS onlinescore
    ,'' AS offlinescore
FROM online
LEFT JOIN offline
    ON online.col1 = offline.col1
    AND online.col2 = offline.col2
WHERE offline.col1 IS NULL
UNION ALL
SELECT offline.col1
    ,offline.col2
    ,'' AS onlinescore
    ,coalesce(offline.score, 0) AS offlinescore
FROM offline
LEFT JOIN online
    ON online.col1 = offline.col1
    AND online.col2 = offline.col2
WHERE online.col1 IS NULL

Upvotes: 0

Related Questions