SQL Language (MOSC)

MOSC Banner

Difference between "in" and "exists" in Oracle 10g and 11g

edited Nov 16, 2011 10:25PM in SQL Language (MOSC) 9 comments

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center