Using MySQL "NOT IN" but allowing for substrings -

i trying query rows not in set of rows. however, other set of rows may contain strings include strings first table.

i'm confusing myself trying explain i'll use following example tables:

mysql> describe tablea; +------------+----------+------+-----+---------+-------+ | field      | type     | null | key | default | | +------------+----------+------+-----+---------+-------+ | name       | char(40) | no   | pri |         |       |  +------------+----------+------+-----+---------+-------+  mysql> describe tableb; +------------+----------+------+-----+---------+-------+ | field      | type     | null | key | default | | +------------+----------+------+-----+---------+-------+ | nametag    | char(40) | no   | pri |         |       |  +------------+----------+------+-----+---------+-------+  mysql> select name tablea; +------------+ | name       | +------------+ | cat        | | dog        | | cow        | +------------+  mysql> select nametag tableb; +------------+ | nametag    | +------------+ | wolf       | | dog        | | browncow   | +------------+ 

i trying find method similar not in operation, because cow "in" browncow, want exclude value.

mysql> select name tablea name not in ( select nametag tableb ); +------------+ | name       | +------------+ | cat        | | cow        | +------------+ # looking return "cat" example. 

is there operation can search rows aren't contained in set additional modifiers?

you use anti-join pattern, predicate matching. (the anti-join outer join, return all rows 1 table, along matches table, , predicate exclude rows had match

  select a.name     tablea     join tableb b       on b.nametag concat('%',a.name,'%')    b.nametag null 

(any rows a had matching row b... row b have non-null value. or, put way... rows a didn't have matching row in b have null value columns b.)

if there's row in a has name='cow', , row b has nametag='browncow', rows match.

the row a name='cat' returned if string 'cat' doesn't appear in values of b.nametag.

note: percent , underscore characters wildcards in like predicate. if want matching on characters, you'd need "escape" backslash. there's similar issues using regexp match, lot more possible mischievous characters.

---

there other query patterns return equivalent result.

for example:

  select a.name     tablea    not exists           ( select 1              tableb b             b.nametag concat('%',a.name,'%')          ) 

personally, prefer anti-join pattern.