Re: tricky query question

In article <khuisman.32.000E846A_at_wnet.gov.edmonton.ab.ca>, khuisman_at_wnet.gov.edmonton.ab.ca (Ken Huisman) writes:
> I have a table with a 1:M relationship to another table.
>
> I would like to do a query on the first table based on whether or not there
> exists related rows in the second table. The closest i have come is to just
> do a join and retrieve the data on the 1 side several times - but i dont want
> this because i only want to retrieve each row once.
>
> i would like something like this:
>
> select id from table1 where id is a member of ( select table1id from table2
> where blah blah blah...)
>
> kind of a set membership kind of thing.
>
> Is there a way to do this?
>
> many thanks.
>
>
> Ken
> khuisman_at_wnet.gov.edmonton.ab.ca
>

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);

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.

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285