Difference between "in" and "exists" in Oracle 10g and 11g
Can anybody explain what is the difference between these two queries:
select a.* from GZP_WB a where gzp_id in (select gzp_id1 from vw_gzp);
select a.* from GZP_WB a where exists (select 1 from vw_gzp where gzp_id1=a.gzp_id);
I get different results in Oracle 11.2
SQL> select a.* from GZP_WB a where gzp_id in (select gzp_id1 from vw_gzp);
no rows selected
SQL> select a.* from GZP_WB a where exists (select 1 from vw_gzp where gzp_id1=a.gzp_id);
OBJECTID GZP_ID
---------- ----------
1 1
In Oracle 10.2 there is no difference, both queries give the same result.