Thanks for clarifying this, David. Learned something new today!
On Mon, 25 Nov 2019 at 15:25, David Raymond <david.raym...@tomtom.com>
wrote:
> There'll be a few differences.
>
> The JOIN version will return 1 row for every item in queries which
> matches, and it will test every single one every time. So if you have in
> the queries table both 'Alex' and 'Alexand' then 'Alexander' and
> 'Alexandra' will each show up twice, once for 'Alex' and once for
> 'Alexand'. Depending on what you're doing this may be what you want.
>
> The EXISTS version will only ever return one row for each record in the
> names table, and it will stop checking other patterns once it finds one
> that matches.
>
> So if you want any info from the queries table then go with the join
> route, if you only care if yes/no there's anything at all that matches,
> then go with exists.
>
>
>
> -----Original Message-----
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
> Behalf Of Gert Van Assche
> Sent: Saturday, November 23, 2019 5:43 AM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] LIKE IN
>
> Both queries will work like this:
>
> DROP TABLE names;
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alex');
> INSERT INTO names VALUES ('Alexander');
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
> INSERT INTO names VALUES ('Rhobin'); -- should not match
> INSERT INTO names VALUES ('Robert');
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';
>
> SELECT name from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
>
> On Sat, 23 Nov 2019 at 11:34, Gert Van Assche <ger...@gmail.com> wrote:
>
> > I think this will work:
> >
> > INSERT INTO queries VALUES ('Alex');
> > INSERT INTO queries VALUES ('Rob');
> >
> > select * from names
> > where exists (
> > select query from queries
> > where names.name like '%'||query||'%'
> > );
> >
> > On Fri, 22 Nov 2019 at 15:19, David Raymond <david.raym...@tomtom.com>
> > wrote:
> >
> >> Or alternatively something like:
> >>
> >> select * from table
> >> where exists (
> >> select query from queries
> >> where table.name like query
> >> );
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
