Re: faster query style

MollyShark <mollyshark@[EMAIL PROTECTED]
> wrote:

>Is there any noticeable speed difference in these two queries?
>
>select * from table1, table2 where table1.field=table2.field
>blahblahblah
>
>select * from table1 where table1.field in (select field from table2)
>
>Both fields are indexed, of course.

     Probably.  The queries are NOT equivalent.

     Consider:

          table1:
               data  plus1 (c(10))
               1     one
               2     two
               3     three

          table2:
               data  plus2 (c(10))
               1     1-1
               3     3-1
               3     3-2

     The first query will return
          data_a  plus1  data_b  plus2
          1       one    1       1-1
          3       three  3       3-1
          3       three  3       3-2
and the second will return
          data  plus1
          1     one
          3     three

     The first query means to take the cartesian join of table1 and
table2 eliminating any cases where table1.field and table2.field are
not equal.  All columns of both tables are included, and there is the
possibility of duplicates if data is not a key.

    The second query means to return those rows in table1 where data
is in table2 as well.  The columns of table2 will not be included, and
there is no possibility of duplicates.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
     I have preferences.
     You have biases.
     He/She has prejudices.