[jayanta_deb]

Case 1:

Select Ename,Job
from Emp
where Deptno IN ( Select Deptno from Dept)

Case 2:

Select Ename,Job
from Emp
where EXISTS ( Select Deptno From Dept)

I have found the Case 2 takes less to time to perform.

I would like to know the reason.
I would appreciate any comments on this.

ThankX

JD

[paginit]

Hi,
I think both the queries are different.
Second query is missing join

It should be

Select Ename,Job
from Emp
where EXISTS ( Select Deptno From Dept where emp.deptno=dept.deptno)

Thanks,

[andrewst]

The reason would be different execution plans - run with AUTOTRACE ON to see the difference.

Actually, as written these queries will produce different results anyway. Presumably you meant to correlate the EXISTS subquery like this:

Select Ename,Job
from Emp
where EXISTS ( Select Deptno From Dept where Dept.deptno = Emp.deptno)

This is what I get:

SQL> set autotrace traceonly
SQL> Select Ename,Job
  2  from Emp
  3  where Deptno IN ( Select Deptno from Dept)
  4  /

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=280)
   1    0   NESTED LOOPS (Cost=1 Card=14 Bytes=280)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=238)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)




Statistics
----------------------------------------------------------
        778  recursive calls
          4  db block gets
        168  consistent gets
         31  physical reads
          0  redo size
        439  bytes sent via SQL*Net to client
        307  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> Select Ename,Job
  2  from Emp
  3* where EXISTS ( Select Deptno From Dept where Dept.deptno = Emp.deptno)
SQL> /

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=17)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=17)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=1
           Bytes=3)


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        452  bytes sent via SQL*Net to client
        307  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

As you can see, the optimizer approached the 2 statements differently.