Search code examples
hadoophivebigdataexists

Error in Hive : For Exists/Not Exists operator SubQuery must be Correlated

select * from students1;

students1.name  students1.age   students1.gpa
fred    35  1.28
barney  32  2.32
shyam   32  2.32

select * from students2;
students1.name  students1.age
fred    35
barney  32

When I am running this query 

select 
name,age from students1
where not exists
(select name,age from students2);

I am getting this bellow error

Error while compiling statement: FAILED: SemanticException line 39:22 Invalid SubQuery expression 'age' in definition of SubQuery sq_1 [ exists (select name,age from students2) ] used as sq_1 at Line 3:10: For Exists/Not Exists operator SubQuery must be Correlated.

Solution

  • The error message is clear. The subquery should be correlated when using exists/not exists.

    select name,age 
from students1 s1
where not exists (select 1
                  from students2 s2
                  where s1.name=s2.name and s1.age=s2.age
                 )