Ah! I see. Thanks for that walk through of OUTER JOIN. Ben
> On Oct 27, 2019, at 4:18 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > > On Sunday, 27 October, 2019 16:09, Benjamin Asher <benashe...@gmail.com> > wrote: > >> Is there an advantage to putting WHERE-type filtering in the >> ON constraint vs leaving it in the WHERE clause for LEFT JOINs? > > The ON clause is merely syntactic sugar for a parenthesized AND clause > appended to the parenthesized WHERE clause EXCEPT in the case of an OUTER > JOIN. > > That is when you emit: > > SELECT <columns> > FROM <table1> > JOIN <table2> > ON <conditions1> > WHERE <whereconditions> > > This is really nothing more than: > > SELECT <columns> > FROM <table1>, <table2> > WHERE (<conditions1>) > AND (<wherecondition>) > > This applies no matter how many <tablen> there are, nor how may ON > <conditionsn> there are. The ON clause does not even have to mention tables > that have already appeared so long as they eventually appear so the WHERE > clause can be processed. JOIN or INNER JOIN is merely an alternate spelling > of a comma, and ON merely puts its argument conditional expression in > parenthesis and tacks it onto the end of the WHERE conditions with an AND > (the where condition clause itself being parenthesized). > > In the case of an OUTER JOIN the ON condition specifies the selection > constraint for descent into the immediately preceding table (that is, the ON > clause binds to the table that is the target of the join). If the selection > criteria in that descent condition cannot be met, that table tuple is > replaced with a tuple of all NULL. > > Once upon a time there was no ON clause, and one would specify outer > constraints with a *. dibble *= dabble means that you want all the values of > dibble even if there is no matching dabble, and the dabble row is therefore > comprised of all nulls (this is a left outer join). Similarly dibble =* > dabble meant that one wants all the dabble even if there is no matching > dibble, in which case the tuple from which the dibble came will be all nulls > (right outer join). There was also a dibble *=* dabble which meant that you > wanted all the results where dibble matched dabble, plus the ones where > dibble had no match and the tuple from when dabble came was therefore all > nulls, and the ones where dabble had no match in which case the tuple from > which dibble came was all nulls (full outer join). > > The JOIN .. ON semantics were invented because some people had difficulty > composing appropriate WHERE clauses without it. > > So for OUTER JOINS (of which a LEFT JOIN is but an instance of the entire > class) whether a condition appears in the ON clause or the WHERE clause is > material to how the query is performed and the results obtained. For an > INNER JOIN, it does not matter whether the condition is specified in an ON > clause or in the WHERE clause. > > In fact, in the case of INNER JOIN you do not need either keyword at all: > > SELECT a, b > FROM x, y > ON x.a == y.c > WHERE y.q == 5 > > is the same as: > > SELECT a, b > FROM x, y > ON y.q == 5 > WHERE x.a == y.c > > is the same as > > SELECT a, b > FROM X JOIN Y > WHERE x.a == y.c > AND y.q == 5 > > which is really just: > > SELECT a, b > FROM x, y > WHERE x.a == y.c > AND y.q == 5 > > however, for outer joins: > > SELECT a, b > FROM x LEFT JOIN y > ON x.a == y.c > WHERE y.q == 5 > > is the same as: > > SELECT a, b > FROM x, y > WHERE x.a == y.c > AND y.q == 5 > > (that is, because the WHERE clause requires that y.q not be NULL, the OUTER > JOIN is meaningless and merely results is more processing CPU and memory > usage than is necessary since the OUTER JOIN is really just an inner join). > > SELECT a, b > FROM x LEFT JOIN y > ON x.a == x.c and y.q == 5 > > gives a completely different set of results. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > > > _______________________________________________ > 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
