RE: SQL oddity

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

I believe (may be wrong) that this subselect is run repeatedly, once for
each row returned from the outermost select. The results from the subselect
are not preserved. This results in a Cartesian product.

This may not be true for all cases, but it can get nasty.

At 09:33 AM 3/13/02 -0600, you wrote:
>I've seen this a lot when using sub selects in a statement.  Consider:
>
>Select * from item_master
>where vendor in (select vnd
>                         from vndmst
>                         where crt_date = current_date)
>
>I believe (conjecture based on observation) that if the sub-query returns a
>small result set, say 30 rows, then the SQL optimizer will just scan the
>result set for a match on each item_master record.  For 1 row of
>item_master, if it did not find a match in the sub-query, the number of
>records process would show 31.
>
>
>Eric DeLong
>Sally Beauty Company
>MIS-Sr. Programmer/Analyst
>940-898-7863 or ext. 1863
>
>