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
