Re: [sqlite] ON conditions vs. WHERE conditions
> > What is the rationale about placing complex conditions in the ON part > > of an inner join rather than in an WHERE clause? > >Except for outer joins, the difference is purely stylistic. They are >functionally equivalent. In fact, SQLite internally rewrites the >former to the latter, before generating the query plan. > >For outer joins (of which SQLite only supports LEFT JOIN), the >distinction is significant. That was my impression and consistent with my real-world findings (wall clock made). Just wanted to avoid a possible pitfall. Outer is another beast. Thanks Igor. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ON conditions vs. WHERE conditions
On Tue, Jul 20, 2010 at 9:43 AM, Jean-Christophe Deschamps wrote: > What is the rationale about placing complex conditions in the ON part > of an inner join rather than in an WHERE clause? My sense is that it is not so much about "complexity" but more about the logic of the process. The ON conditions are a part of the description of the "source table," which is really a virtual table made up by JOINing several tables using certain conditions, the ON conditions. Once the virtual table is described, you tell what to get out of that (the SELECT conditions), and tell how to restrict/filter that information (the WHERE conditions). > > I understand that the ON clause will limit the temporary table > processed by a subsequent WHERE clause, while a larger table will be > filtered by the WHERE part if no selective ON clause is present. > But with a complex condition (20+ sub-conditions on various part of > joined tables) requiring full tables scan, aren't both solutions > essentially equivalent? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ON conditions vs. WHERE conditions
Jean-Christophe Deschamps wrote: > What is the rationale about placing complex conditions in the ON part > of an inner join rather than in an WHERE clause? Except for outer joins, the difference is purely stylistic. They are functionally equivalent. In fact, SQLite internally rewrites the former to the latter, before generating the query plan. For outer joins (of which SQLite only supports LEFT JOIN), the distinction is significant. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ON conditions vs. WHERE conditions
What is the rationale about placing complex conditions in the ON part of an inner join rather than in an WHERE clause? I understand that the ON clause will limit the temporary table processed by a subsequent WHERE clause, while a larger table will be filtered by the WHERE part if no selective ON clause is present. But with a complex condition (20+ sub-conditions on various part of joined tables) requiring full tables scan, aren't both solutions essentially equivalent? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users