Re: tricky query question

> 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. Received on Tue Nov 08 1994 - 16:17:44 CET