'Hive SQL: How to create flag occurrence while join with other table

I want to check whether my member from table A present in table B or not? Here is the problem is Both Table A and Table B has millions of records and table B have duplicate records. So that i cann't do left join. it takes hours to run.

Table A

enter image description here

Table B

enter image description here

Output

enter image description here

mysqlsqlhivehiveql


Solution 1:[1]

use this :

select member,
case when EXISTS (select 1 from TableB where TableB.member = tableA.member) then 1 else 0 end as Flag 
from tableA

Solution 2:[2]

Not a very good solution but you can try this.
So, we use not in or not exists to get one set of data and then use in or exists to get another set. And then union them all together to get complete set.

select 
a.* , 0 flag
from tableA a where member not in ( select member  from tableB)
union all 
select 
a.* , 1 flag
from tableA a where member in ( select member  from tableB)

The trick may be, you can run 2 separate SQL for this and will get perf benefit instead of union all.

Not exist will work same way but can give you better performance.

SELECT a.*, 0 flag
FROM tableA a
WHERE NOT EXISTS(
    SELECT 1 FROM tableB b WHERE (a.member=b.member))
union all 
SELECT a.*, 1 flag
FROM tableA a
WHERE EXISTS(
    SELECT 1 FROM tableB b WHERE (a.member=b.member))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Piyush Kachhadiya
Solution 2

Related Questions