MySQL - NOT IN LIKE

mysql sql-like in-operator not-operator
James T picture James T · Feb 27, 2012 · Viewed 8.1k times · Source

How can I, in mysql, check if a value is inside a number of fields in another table?

Something like

SELECT * FROM table WHERE concat('%',value,'%') NOT LIKE IN(SELECT field FROM anothertable)

But I don't think that's quite right, is it?

Answer

Ian Clelland picture Ian Clelland · Feb 27, 2012

No, not quite.

SELECT * FROM table WHERE NOT EXISTS (
    SELECT * from anothertable WHERE field LIKE CONCAT('%',value,'%')
)

will probably do it. Assuming that value is a column on table, and field is the corresponding column on anothertable which may or may not contain value as a substring.

Be warned, though -- this is going to be a very slow query, if anothertable contains many rows. I don't think there's an index that can help you. MySQL will have to to a string-comparing table scan of anothertable for every row in table.

Related questions

How can I search (case-insensitive) in a column using LIKE wildcard?

I looked around some and didn't find what I was after so here goes. SELECT * FROM trees WHERE trees.`title` LIKE '%elm%' This works fine, but not if the tree is named Elm or ELM etc... How do …

Read More
MySQL Like multiple values

I have this MySQL query. I have database fields with this contents sports,shopping,pool,pc,games shopping,pool,pc,games sports,pub,swimming, pool, pc, games Why does this like query does not work? I need the fields with …

Read More
MySQL: NOT LIKE

I have these text in my db, categories_posts categories_news posts_add news_add And I don't want to select the rows with categories, I use a query something like this, SELECT * FROM developer_configurations_cms WHERE developer_configurations_…

Read More