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?

There are basically three approaches:

1. use of the "IN" operand

SELECT deptno, dname
FROM dept
WHERE deptno in (

        SELECT  deptno
        FROM    emp);

This approach works best when the child table (in this case EMP) is relatively small since you are unavoidably doing a full-table scan on it.

2. use of the "EXISTS" operand

SELECT deptno, dname
FROM dept d
WHERE EXISTS (

        SELECT  NULL
        FROM    emp e
        WHERE   e.deptno = d.deptno
        AND     rownum = 1

This approach works best when the child table is much larger than the parent table since you can limit to a index-only scan of the child table by having the proper index (deptno on emp). Of course, note that now you are doing a full-table scan of the parent table.

3. use "DISTINCT" to screen out duplicate rows

SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE e.deptno = d.deptno;

This approach looks similar to the second approach but if you have a lot of records per parent record (say 1000 employees per department), you have to fetch multiple records from the database before grouping them together.

-- 
"And this is all I have to say about that..."

                                    `o<'
---------------------------------oo0(__)0oo--------------------------------
   __    __  __  __  __  _  _    __   U  __  _ __ _
  /__\  (  )(  )(  )(  )( \( )  (  )    /__\( (  ) )  "You may be an idoit,
 /(__)\  )(__\ () /  )(  )  (    )(__  /(__)\\  / /    but try not to be
(__)(__)(____)\__/  (__)(_)\_)  (____)(__)(__)\/\/     stupid."   - F. Gump