Understood. Thank you for discussing this with me. Your help is greatly appreciated.
-- Rich On Thu, Dec 3, 2009 at 5:03 PM, Pavel Ivanov <[email protected]> wrote: >> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 >>>> [email protected] >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>> _______________________________________________ >>> sqlite-users mailing list >>> [email protected] >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
