Re: Proper Join and check

Patrick Nelson wrote:
> Billy G. Allie wrote:
> ----------------->>>>
> The query you are looking for is:
>
> select a.sym from tableone a
> where a.sym not in (select b.sym from tablemany b
> where b.sym = a.sym);
>
> This query will run MUCH faster if you create a secondary index for
> tablemany (in fact, don't even try it without the index for any significant
> number or rows):
[. . .]
> Oh yeah it helped... Thanks the query took like 4 seconds. I'm not sure I
> totally understand the b.sym = a.sym though.

With the b.sym = a.sym, the query optimizer will use an index scan of tablemany.
Without it, a sequential scan will be used (very VERY S L O W for any signifi-
cant number of rows).

Without the b.sym = a.sym, the result of the subselect will be all the rows in
tablemany which will have to be scanned to see if the test (not in) succeeds.
With the b.sym = a.sym, the result of the subselect will be empty or the value
of b.sym (as determined by an index lookup, which is why the index was needed).

I hope this clarifies things somewhat.
___________________________________________________________________________
____ | Billy G. Allie | Domain....: Bill(dot)Allie(at)mug(dot)org
| /| | 7436 Hartwell | MSN.......: B_G_Allie(at)email(dot)msn(dot)com
|-/-|----- | Dearborn, MI 48126|
|/ |LLIE | (313) 582-1540 |