> TabA.ID1 > TabA.ID2 > TabA.field1 > > TabB.ID1 > TabB.ID2 > TabB.field2 > > TabC.ID1 > TabC.field3 > > ViewBC: > SELECT * FROM TabB INNER JOIN TabC On TabB.ID1 = TabC.ID1 > > This is slow: > SELECT field1, field2, field3 from TabA LEFT OUTER JOIN ViewBC ON TabA.ID1 = > ViewBC.ID1 AND TabA.ID2 = ViewBC.ID2 > > This is fast: > SELECT field1, field2, field3 from TabA LEFT OUTER JOIN TabB ON TabA.ID1 = > TabB.ID1 AND TabA.ID2 = TabB.ID2 INNER JOIN TabC ON TabB.ID1 = TabC.ID1
The second query may be faster, but the two queries are not equivalent. Outer joins are not associative. CREATE TABLE t1(a); INSERT INTO t1 VALUES(1); CREATE TABLE t2(b); INSERT INTO t2 VALUES(2); CREATE TABLE t3(c); INSERT INTO t3 VALUES(2); create view v1 as select b,c from t2 join t3 on b=c; sqlite> select a,b,c from t1 left join v1 on a=b; a b c ---------- ---------- ---------- 1 NULL NULL sqlite> select a,b,c from t1 left join (t2 join t3 on b=c) on a=b; a b c ---------- ---------- ---------- 1 NULL NULL sqlite> select a,b,c from t1 left join t2 on a=b join t3 on b=c; sqlite> select a,b,c from (t1 left join t2 on a=b) join t3 on b=c; ____________________________________________________________________________________ Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
