sql - Why is join the restricted tables much faster than restricting the joined tables? -

i'm new in database algorithms , don't know how works internally.

so, tried write following 2 queries compare performance:

select *  (     select *     account     id < 20000 ) dpa_r full outer join (     select *     transactions     id < 20000 ) pet_r on some_condition full outer join (     select *     profit     id < 20000 ) dp_r on some_condition  offset 200000 limit 20 

the query execution time = 940ms.

i rewrote query follows:

select *  (     select *     account ) dpa_r full outer join (     select *     transactions ) pet_r on some_condition full outer join (     select *     profit ) dp_r on some_condition dp_r.id < 20000 , dpa_r.id < 20000 , pet_r.id < 20000 offset 200000 limit 20 

the query execution time 17321ms.

why there difference in time?

you're 2 queries give different results, 1 of them outer join query, other 1 inner join query. reason why different execution times.

with column conditions in where clause, outer joins perform regular inner joins:

sql>create table t1 (c1 int, c2 char(10)); sql>create table t2 (c1 int, c2 char(10)); sql>insert t1 values (1,'one'); sql>insert t1 values (2,'two'); sql>insert t2 values (1,'one'); sql>insert t2 values (5,'five'); sql>select * t1 left join t2 on t1.c1 = t2.c1 sql&where t1.c1 < 10 , t2.c1 < 10;          c1 c2                  c1 c2 =========== ========== =========== ==========           1 one                  1 one                    1 row found  sql>select * t1 left join t2 on t1.c1 = t2.c1  sql&  , t1.c1 < 10 , t2.c1 < 10;          c1 c2                  c1 c2 =========== ========== =========== ==========           1 one                  1 one           2 two                  - -                    2 rows found 

when conditions moved on clause, true outer join behavoir!