On Tue, Jul 20, 2010 at 9:43 AM, Jean-Christophe Deschamps <j...@q-e-d.org> 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
