differance between in,exists ,= in correlated subqueries

UDA
Enthusiast

differance between in,exists ,= in correlated subqueries

Can any one give the differanace between using of in,exists,= in correlated subqueries

What is the difference between these queries in performance wise(by considering spool space also)

Example:

sel * from a where col1=(sel col1 from b where a.col1=b.col1);

sel * from a where col1 in(sel col1 from b where a.col1=b.col1)

sel * from a where exists (sel col1 from b where a.col1=b.col1)
1 REPLY
Senior Apprentice

Re: differance between in,exists ,= in correlated subqueries

Hi bharath111,

there shouldn't be any difference (just check EXPLAINs), but the first one could fail if there's more than one row per value.

Dieter