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

Question

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.

Vamsi Prabhala Vamsi Prabhala · Accepted Answer · 2018-08-09T12:35:46

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
                 )

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

2 Answers