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

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

Table B

Output

CodePudding user response：

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

CodePudding user response：

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))