CODEWITHSUNDEEP
hivehiveql
Rocky
Rocky

Reputation: 51

How to fetch the unmatched records from two tables in Hive?

I have below two tables of data where i need to get only the unmatched records of data using hive only.

Table1:

hive> select * from dept;
OK
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHICAGO
40      OPERATIONS      BOSTON

Table2:

hive> select * from dept_text;
OK
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHICAGO
40      OPERATIONS      BOSTON
50      Software        Bangalore
60      Housewife       yellandu

Output:I need to get the output like below.Can someone help me on this.

50      Software        Bangalore
60      Housewife       yellandu

Upvotes: 1

Views: 874

Answers (1)

notNull
notNull

Reputation: 31490

Use left join on dept_text table then filter only the null id columns from dept table

select dt.* from dept_text dt 
    left join 
dept d 
  on d.id=dt.id 
where d.id is null;

Example:

desc dept;
--id                    int
--desc                  string
--city                  string

select * from dept;
--OK
--dept.id   dept.desc   dept.city
--10    ACCOUNTING  NEW YORK
--20    RESEARCH    DALLAS
--30    SALES   CHICAGO
--40    OPERATIONS  BOSTON

--if you want to join on desc column

select dt.* from dept_text dt 
left join 
dept d 
on d.desc=dt.desc 
where d.id is null;

--or if you want to join on id column

select dt.* from dept_text dt 
left join 
dept d 
on d.id=dt.id 
where d.id is null;

Upvotes: 1

Related Questions