Re: tricky query question

Jason Kerr (jkerr_at_wvnvms.wvnet.edu) wrote:
: > My understanding is that the EXISTS operator is for exactly this type of thing.
: >
: > The example that is documented on page 3-6 of the ORACLE7 Server SQL Language
: > Reference Guide shows how to use EXISTS.
: >
: > select dname, deptno
: > from dept
: > where exists ( select *
: > from emp
: > where dept.deptno = emp.deptno);
: >
: > Note that this is a correlated subquery. The inner query will get executed once
: > for every row in the outer query. My understanding is that it would go quickly
: > because the subquery stops as soon as it gets 1 row.
: >
: > Another option that might do what you want is to do a join, then use
: > DISTINCT to get rid of duplicate rows.
: >
: > select distinct dname, dept.deptno
: > from dept, emp
: > where dept.deptno = emp.deptno;
: >
: > I haven't done this type of thing enough to give you a recommendation on which
: > of these two methods would give the better performance. My guess would be that
: > the option that uses EXISTS would run faster, because you might not be reading
: > as many rows, and because you are not sorting to remove duplicates.
: >
 

: Another approach might be as follows:
 

: select dname, deptno
: from dept
: where deptno in ( select deptno
: from emp
: where dept.deptno = emp.deptno);
 

: This should be a fast approach if you have an index on deptno on both tables.

: You can try both with trace on to determine which would be better.

--

----------------
John Willing