Jens Alfke wrote: > SELECT DISTINCT user.name > FROM user, json_each(user.phone) > WHERE json_each.value LIKE '704-%';
0|0|0|SCAN TABLE user 0|1|1|SCAN TABLE json_each VIRTUAL TABLE INDEX 1: 0|0|0|USE TEMP B-TREE FOR DISTINCT > For my purposes it seems cleaner to use a nested SELECT with EXISTS: > > SELECT user.name > FROM user > WHERE EXISTS( SELECT 1 FROM json_each(user.phone) WHERE json_each.value > like ‘704-%’ ) 0|0|0|SCAN TABLE user 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SCAN TABLE json_each VIRTUAL TABLE INDEX 1: > Is this going to be any less efficient? The join and the correlated subquery are executed in the same way. The first query stores all unique name values in a temporary index; the second query is more efficient because it not only avoids this, but also because the EXISTS implies a LIMIT 1. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
