Re: [sqlite] ON conditions vs. WHERE conditions

[Jean-Christophe Deschamps]

> > 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

[P Kishor]

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

[Igor Tandetnik]

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