CODEWITHSUNDEEP
sqljoindb2where-clause
hopex40
hopex40

Reputation: 1

Inner join with where conditions, which will excute first? Join or where conditions?

For example1: 

select T1.*, T2.* 
from TABLE1 T1, TABLE2 T2
where T1.id = T2.id
  and T1.name = 'foo'
  and T2.name = 'bar';

That will first join T1 and T2 together by id, then select the records that satisfy the name conditions? Or select the records that satisfy the name condition in T1 or T2, then join those together?

And, Is there a difference in performance between example1 and example2(DB2)?

example2:

select * 
from  
(
  select * from TABLE1 T1 where T1.name = 'foo'
)  A, 
(
  select * from TABLE2 T2 where T2.name = 'bar'
)  B  
where A.id = B.id;

Upvotes: 0

Views: 155

Answers (2)

Guffa
Guffa

Reputation: 700372

How the query will be executed depends on what the query planner does with it. Depending on the available indexes and how much data is in the tables the query plan may look different. The planner tries to do the work in the order that it thinks is most efficient.

If the planner does a good job, the plan for both queries should be the same, otherwise the first query is likely to be faster because the second would create two intermediate results that doesn't have any indexes.

Upvotes: 1

Max
Max

Reputation: 2552

Exemple 1 is more efficient because it has no embedded queries. About how the result set is build, I have no idea - I don't know DB2.

Upvotes: 0

Related Questions