Rich Rattanni wrote:
> Awesome! Thank you. Can I ask a follow up question? "from foo,
> bar"... is this behaving like a join?
Yes it does. You can also phrase the same query with an explicit join:
select bar.* from bar join foo
on (foo.col2 = bar.col2 and foo.col3 = bar.col3);
> Second, I came up with my own solution:
>
> select * from foo
> where col1 || col2 in (select col1 || col2 from bar)
Note that, with this condition, a pair ('AB', 'C') would match ('A', 'BC').
> 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)).
How did you arrive at the conclusion that this last query is better than
O(n^2)? Forget about concatenation: a simple query like
select * from foo where col1 in (select col1 from bar);
is n^2 unless an index can be used.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
