0

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;
Bonifacio2
  • 3,405
  • 6
  • 34
  • 54
hopex40
  • 1
  • 1
  • 1
    Example 1 is definitely more efficient. Try to avoid embedded select statements whenever and wherever possible. – Tricky12 Aug 28 '13 at 17:16
  • As for the first part of the question. It must perform the JOIN first, otherwise it would not be able to SELECT based on the WHERE clauses. – Tricky12 Aug 28 '13 at 17:25
  • Maybe you should take a look at [this question](http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line) – Bonifacio2 Aug 28 '13 at 17:35
  • DB2 will create the same plan for both of those queries. That said, I would **definitely** use #1, because it's way more readable. – bhamby Aug 29 '13 at 14:00
  • Same plan,thanks for your remind! – hopex40 Sep 05 '13 at 14:50

2 Answers2

1

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.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
0

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.

Max
  • 2,508
  • 3
  • 26
  • 44