They are not semantically equivalent. join conditions attached to an outer join operation are not semantically equivalent to the same conditions being in the where clause.
In other words: select a,b,c from a join b join c on a.a=b.b where c.c=b.d is simply syntactic sugar for select a,b,c from a, b, c where a.a=b.b and c.c=b.d; In all cases the conditions in ON clauses of INNER JOINS are nothing more than WHERE clause filters. You do not even have to have the tables used in the ON clause "referenced" at the point you refer to them. the word "INNER JOIN" is syntactic sugar for a comma (,), and ON is sytactic sugar for the word WHERE (or AND). However, for OUTER JOINS the conditions in the ON clause "glue themselves" to the OUTER JOIN operation and ARE NEITHER syntactically or symantically the same as WHERE clause conditions. That is to say the behaviour observed is how it is designed to work and you expectations are misguided. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dinu >Sent: Thursday, 4 January, 2018 12:29 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] LEFT JOIN + WHERE / OR optimisation > >Hi all, >I've ran into an optimisation problem with a double-left join that >works as >an "either" clause. > >The query is as follows: > >SELECT * >FROM > a >LEFT JOIN > b ON <cond> >LEFT JOIN > c ON <cond> >WHERE > b.someId IN (1,2,3) OR > c.someId IN (4,5) > >This results in a bloated execution plan: >SEARCH a >SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX >SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX > >However, the semantically equivalent: >SELECT * >FROM > a >LEFT JOIN > b ON <cond> AND b.someId IN (1,2,3) >LEFT JOIN > c ON <cond>AND c.someId IN (4,5) >WHERE > b.someId IS NOT NULL OR > c.someId IS NOT NULL > >Gets the proper execution plan: >SEARCH b >SEARCH c >EXECUTE LIST SUBQUERY > > > >-- >Sent from: http://sqlite.1065341.n5.nabble.com/ >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
