| View previous topic :: View next topic |
| Author |
Message |
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
 Posted: Thu Jul 23, 2009 2:31 pm Post subject: Joining 3 tables |
 |
|
Suppose i am joining 3 tables based on 1 column COL_A, i have a question
Is it enough if i write a query like this
| Code: | SELECT COL1,COL2,COL2
FROM TBL1, TBL2, TBL3
WHERE TBL1.COL_A = TBL2.COL_A
AND TBL2.COL_A = TBL3.COL_A |
or do i need to specify all relations between all the tables like this
| Code: | SELECT COL1,COL2,COL3
FROM TBL1, TBL2, TBL3
WHERE TBL1.COL_A = TBL2.COL_A
AND TBL2.COL_A = TBL3.COL_A
AND TBL1.COL_A = TBL3.COL_A. |
Is there any advantage in writing the query as the 2nd way? or the 1st one is no better than the 2nd?
_________________
Thanks |
|
| Back to top |
|
 |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Thu Jul 23, 2009 3:05 pm Post subject: |
|
|
I would write it like this:
| Code: |
Select A.col1, b.col2,c.col3
from table1 a
inner join table2 b on a.col1 = b.col2
inner join tbale3 c on a.col1 = c.col3
where a.col1 = ??????
|
This would return if a.col1 is in Table2 and Table3
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Thu Jul 23, 2009 3:10 pm Post subject: |
|
|
hi NASCAR, thanks for your info
But what i wanted to know was in the 2 queries that i had written, will Query 2 in any way be more efficient that Query 1? I'm not writing new queries in my application but have been asked to tune existing queries.. n then i had this doubt..
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 23, 2009 3:34 pm Post subject: |
|
|
edkir98,
I think it depends on how you want the result set. Do you need the results if the column matches on ALL the 3 tables?
If that is the case then you just need to check with table 1 key
| Code: |
SELECT TBL1.COL1
,TBL2.COL2
,TBL3.COL3
FROM TBL1
,TBL2
,TBL3
WHERE TBL1.COL_A = TBL2.COL_A
AND TBL1.COL_A = TBL3.COL_A
|
As for the efficiency run an EXPLAIN against all the 3 queries and see how the indexes are being used.
Kolusu |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Fri Jul 24, 2009 3:38 am Post subject: |
|
|
You don't have to code the second version of your query, since DB2 itself will add that additional predicate.
regards
Christian |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Fri Jul 24, 2009 8:00 am Post subject: |
|
|
Thank you Christian and Kolusu!
_________________
Thanks |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
