At 12:12 -0600 1/28/02, BD wrote:
>I have 2 tables, detail and product. The detail table could have
>millions of rows. The product table could have a few hundred. I need
>to know which products are in the detail table. (It would also be
>nice to know which products aren't in the detail table). The problem
>is it has to be fast, very fast. Since it is on a web server it
>can't tie up the CPU for several seconds while it needlessly returns
>thousands of records. I only need to know which fields from one
>table *exist* in the other table. Sounds simple right?
>Ideally it would look like:
> select product_id from products where prod_id in (select prod_id
>from detail where <somedetailwhere>);
>The <somedetailwhere> is an optional where clause that could be
>applied to the detail table. It will use indexes so it will be quite
>Now if I try a simple join like:
> select prod_id from products, detail where <somedetailwhere> and
>product.prod_id = detail.prod_id;
>it will of course return duplicate prod_id's because the product
>could appear in tens of thousands of detail items. I don't need to
>return thousands of rows. I only need to return 1 row of each
>prod_id if that prod_id appears in the detail table.
>I can't use:
> select distinct prod_id from products, detail where
><somedetailwhere> and product.prod_id = detail.prod_id;
>because it takes too long. It will still returns hundreds of
>thousands of rows unnecessarily.
That's impossible. You said your product table contains only a few
This query can't return more rows than are in the product table.
>So is there a solution to this "simple" problem?