The ON condition is used <before> the "add one result row for each row of the outer table where nothing matches the ON condition" The WHERE condition is used <after> those rows are added.
Example with the basic "not in" type of outer join: SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table a (x); sqlite> create table b (x); sqlite> insert into a values (1), (2), (3); sqlite> insert into b values (2); sqlite> select a.x from a left outer join b on a.x = b.x where b.x is null; x 1 3 sqlite> select a.x from a left outer join b on a.x = b.x and b.x is null; x 1 2 3 sqlite> select a.x from a left outer join b where a.x = b.x and b.x is null; sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Thursday, January 04, 2018 2:53 PM To: SQLite mailing list Subject: Re: [sqlite] LEFT JOIN + WHERE / OR optimisation 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
