subqueries or join

[Guest_imported]

I need to extracts somme records from a table that match some requierments (stored in other tables)

What is the most efficient way to do this ?

subqueries :
select bla
from table1
where table1.foo in (select foo from table2 where table2.bar='T')

or by doing join
select bla
from table1,table2
where table1.foo = table2.foo and table2.bar ='T'

thnanks by advance !!

Jean-francois PARIS

 

[grega]

Joins.

Greg.

 

[carp]

I think it depends a lot on how many rows are in each table, how many rows you anticipate being returned, how many rows in one table correspond to a given row in the other table, and how your indexes are set up.

The IN operator is liable to be relatively slow, since it will seek out all values. On the other hand, a correlated subquery using EXISTS might actually be faster than joining the tables:

select bla
from table1 a
where EXISTS (select foo from table2 b
where b.foo = a.foo
AND table2.bar='T');

I have had situations where JOINs took several minutes while EXISTS took less than 5 seconds. I have also seen the reverse hold true. As is so often the case, I think you will have to try it both ways and see which works better for your particular situation.