seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
 Posted: Wed Oct 07, 2009 1:09 pm Post subject: Does Sequence matter for INNER JOIN or LEFT OUTER JOIN |
 |
|
Hi,
I am running an explain onto a query where I am puzzled to see that the number of columns that match for a table changes if I change the sequence of INNER JOIN and LEFT OUTER JOIN. i.e., instead of first INNER JOIN and then LEFT OUTER JOIN, if I put first LEFT OUTER JOIN and then INNER JOIN and the number of columns that matches, changes. Could somebody help decipher this puzzle for me.
if you have still not understood, below are some gory details.
details: Query is extremely big so showing a sample type for reference.
| Code: | Table1 (GTT) - 3 columns: A, B, C Index-none (It's a GTT)
Table2 - 4 columns: A, B, P, Q Index-A,B
Table3 (GTT) - 1 columnS: A Index-none (It's a GTT)
Table4 (GTT) - 4 columns: A, B, C, E Index-none (It's a GTT) |
GTT - Global Temporary Table
You would have noted that column A is common in all 4 tables and similarly other columns as shown.
Query:
| Code: | Select
table1.A
,table1.B
,table1.C
,table2.P
,table2.Q
,COALESCE(table4.E,0)
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.A = TABLE2.A
AND TABLE1.B = TABLE2.B
INNER JOIN TABLE3
ON TABLE1.A = TABLE3.A
LEFT OUTER JOIN TABLE4
ON TABLE1.A = TABLE4.A
AND TABLE1.B = TABLE4.B
AND TABLE1.C = TABLE4.C |
While running explain, I am getting 1 column match on Table2.
But If I change the query and bring the INNER JOIN TABLE2 at the end of the query like below, the column match is fine with 2 columns. Can somebody explain why ?
| Code: | Select
table1.A
,table1.B
,table1.C
,table2.P
,table2.Q
,COALESCE(table4.E,0)
FROM TABLE1
INNER JOIN TABLE3
ON TABLE1.A = TABLE3.A
LEFT OUTER JOIN TABLE4
ON TABLE1.A = TABLE4.A
AND TABLE1.B = TABLE4.B
AND TABLE1.C = TABLE4.C
INNER JOIN TABLE2
ON TABLE1.A = TABLE2.A
AND TABLE1.B = TABLE2.B |
_________________
Thanks. |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
