> Can I ask a follow up question? "from foo, > bar"... is this behaving like a join? I saw this before and didn't > quite understand it, I just realized it permuted all rows of the each > table together (which sounds like a join with no conditions).
Yes, it is join. What you seem to talk about is a cartesian product and this would be it if it wasn't WHERE clause. With WHERE clause it is inner join - SQLite gets smaller of two tables (foo and bar) and for each row in it scans another table for rows satisfying WHERE condition. And this behavior is the same as for your query if your foo table is smaller than bar. But in case if you create index on (col2, col3) in biggest of the tables foo or bar then my query will work an order of magnitude faster than yours. Pavel On Thu, Dec 3, 2009 at 4:54 PM, Rich Rattanni <ratta...@gmail.com> wrote: > Awesome! Thank you. Can I ask a follow up question? "from foo, > bar"... is this behaving like a join? I saw this before and didn't > quite understand it, I just realized it permuted all rows of the each > table together (which sounds like a join with no conditions). > > Second, I came up with my own solution: > > select * from foo > where col1 || col2 in (select col1 || col2 from bar) > > This also works, but from an efficiency standpoint I assume this is > horrible since it would basically be a series of string compare > operations. However, if the number of rows in each table is VERY > LARGE (lets say 50,000) would my solution maybe outperform the first > (on the surface seems like n^2 vs n*S where S is concat string length > (which will always be < 50)). > > On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> Maybe >> >> select distinct bar.* from foo, bar >> where foo.col2 = bar.col2 and foo.col3 = bar.col3 >> >> It's not clear from your requirements written below whether you need >> 'distinct' here or not but I've added it just in case... >> >> >> Pavel >> >> On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni <ratta...@gmail.com> wrote: >>> Suppose I have the following two tables >>> >>> foo: >>> 10 | A | A >>> 20 | B | B >>> 30 | C | C >>> >>> and >>> >>> bar: >>> 1 | A | X >>> 2 | B | C >>> 3 | A | A >>> 4 | C | A >>> 5 | B | B >>> >>> >>> I want to select all the rows in table bar where the second and third >>> column match an entry found in foo (that is to say, I want my result >>> to be 3 | A | A and 5 | B | B. >>> >>> My attempt of: >>> select * from bar >>> where col2 in (select col2 from foo where bar.col2 = col2) >>> and col3 in (select col3 from foo where bar.col3 = col3) >>> >>> Does not work, and I understand that is should not (it returns 2 | B | >>> C, 3 | A | A, 4 | C | A, 5 | B | B). >>> >>> Could someone give me a hand? >>> >>> -- >>> Rich >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
