On 11/18/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > > > > When you add multiple tables into the FROM clause, you make a single > > conceptual table out of them by using the JOIN operator. > > > So > > (with c1,c2,c3 all being rtrees) > > select * from (select * from c1,c2,c3) where bla>10 > > is *not the same as > > select * from c1 where bla>10 > union all > select * from c2 where bla>10 > union all > select * from c3 where bla>10 > ... >
no. See that inner (SELECT * FROM c1, c2, c3)? That is doing a cartesian product of the tables right there. Every row in every table is being combined with every row in every table. The second example is nearer to what you want, but you will have to specify your WHERE clause over and over again. > > > > If you want the same query out of a number of different tables that > > are identical, and join the results together, then you use the UNION > > clause... in other words, you do indeed perform the SELECTs separately > > and then UNION them together, but you have to apply the WHERE > > constraint only once because all the tables are identical. > > > > > Do you mind elaborating on this point? Is that what Igor wrote me? > Igor showed you a way whereby you have to specify your WHERE clause only one. In effect, by doing a SELECT * FROM c1 UNION SELECT * FROM c2 UNION ... You are creating one mongo table and then applying the WHERE clause to this one mongo table. Another way of thinking about it is that (SELECT * FROM c1, c2, c3...) is like all the tables stacked side by side making a very wide table. That is not what you want. But (SELECT * FROM c1 UNION SELECT * FROM c2 UNION...) is like c1 and c2 and c3, etc. stacked on top of each other making a very long table. Once you have a bigger table which is identical in structure to its component tables, it is a simple matter of applying WHERE to this table only once. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
