Subject Re: [firebird-support] Sql query question
Author Ann W. Harrison
At 04:51 PM 11/16/2004, jasajona wrote:

>I have query:
> select field1,
> (select table2.field1 from table2 where field3=table1.field1),
> field3
> from table1
> where field4=6
>How this query works? When that subselect (how this subquery is called?)
>will be executed, before applying condition field4=6 or after?


>How many times this subquery will be executed (for all table1 or just
>for field4=6 records)?

Only for qualifying rows in table1.

>What about performance, what should I avoid writting such

As a general thing, one prefers to write queries with joins rather
than subselects. It's more elegant and gives the query optimizer
something to think about.

select t1.f1, t2.f1, t1.f3
from table1 t1
left outer join table2 t2 (on t2.f3=t1.f1)
where t1.f4 = 6

If you don't want rows from t1 that don't have matching rows
in t2, leave out the "left outer" and give the query optimizer
something more to work on.