MYSQL use 'LIKE' in 'WHERE' clause to search in subquery

Question

How would you use 'LIKE' to search in a subquery?

E.g. i've tried doing this, but doesn't work:

SELECT *
FROM mytable
WHERE name
    LIKE '%
        (SELECT name FROM myothertable)
        %'

I have this so far:

SELECT * FROM t1
WHERE t1.name IN (SELECT t2.name FROM t2)
AND (t1.title IN (SELECT t2.title FROM t2)
    OR t1.surname IN (SELECT t2.surname FROM t2))

It's working ok as it returns exact matchs, but it doesn't seem to return my other records that are similar, so I would like to also check that:

t1.title LIKE '%t2.title%' AND t1.surname LIKE '%t2.surname%'

How would i do this?

only two little correction in your code. 1st remove % and like.Second add in after name, full code is in my answer. — Ankit Sharma, Apr 05 '12 at 06:39
THANK YOU ALL FOR YOUR SPEEDY RESPONSES SO FAR ^_^. @zerkms - about 10 but this doesn't really affect what i'm trying to do, i just want to compare a few fields. — qwerty, Apr 05 '12 at 07:39
@Jason_vorhees - I've edited your response to include what I have tried to do so far. What I want to do now is to try and do a query for LIKE '% %' — qwerty, Apr 05 '12 at 07:46
you cannot do like this "LIKE '%(SELECT name FROM myothertable)%'" try `IN` instead of LIKE. — Ankit Sharma, Apr 05 '12 at 07:49

score 34 · Answer 1 · answered Apr 05 '12 at 03:46

34

Using a JOIN:

SELECT a.*
  FROM mytable a
  JOIN myothertable b ON a.name LIKE CONCAT('%', b.name, '%')

...but there could be duplicates, if there's more than one match in myothertable for a given mytable record.

Using EXISTS:

SELECT a.*
  FROM mytable a
 WHERE EXISTS (SELECT NULL 
                 FROM myothertable b 
                WHERE a.name LIKE CONCAT('%', b.name, '%'))

Using Full Text Search MATCH (requires myothertable is MyISAM)

SELECT a.*
  FROM mytable a
  JOIN myothertable b ON MATCH(a.name) AGAINST (b.name)

answered Apr 05 '12 at 03:46

OMG Ponies

325,700
82
523
502

@bernie: I give options, but EXISTS is probably the best choice -- you were first, +1 – OMG Ponies Apr 05 '12 at 03:56
Perfect answer. Just a small note: `MATCH` probably could produce not actually expected result – zerkms Apr 05 '12 at 03:57

score 13 · Answer 2 · answered Apr 05 '12 at 03:38

13

For example:

SELECT a_column
FROM   mytable t
WHERE  EXISTS (
           SELECT 1
           FROM   myothertable ot
           WHERE  t.`name` LIKE '%' || ot.`name` || '%');

As far as terminology goes: this is known as a correlated subquery.

answered Apr 05 '12 at 03:38

mechanical_meat

163,903
24
228
223

1

And if we go further - every subquery in mysql is correlated ;-) (or more precise: optimizer rewrites every subquery to a correlated one) – zerkms Apr 05 '12 at 03:41
2

@zerkms: `EXISTS` doesn't execute as a correlated subquery – OMG Ponies Apr 05 '12 at 03:47
@OMG Ponies: Ok, but I didn't say it does ;-) – zerkms Apr 05 '12 at 03:56
1

@zerkms: I'm doing my best to implicate you? :p – OMG Ponies Apr 05 '12 at 03:59
@OMG Ponies: yep ;-) Btw, that information was really interesting. I always thought that `EXISTS` is optimized in the similar way to `IN (subquery)` – zerkms Apr 05 '12 at 04:01
@zerkms: Some ways, yes. But EXISTS doesn't care what happens in the SELECT clause, and short-circuits on the first match which makes it a better choice if there's duplicates. – OMG Ponies Apr 05 '12 at 04:03

score 2 · Answer 3 · answered Sep 05 '14 at 07:55

2

Just another way:

select a.field, b.code
from table1 a 
inner join (select code from table2 where ....) b on a.field like CONCAT('%', b.code, '%')

answered Sep 05 '14 at 07:55

David

362
2
7

score 2 · Answer 4 · edited May 07 '20 at 09:55

2

this string works fine for me.

"SELECT * FROM table1 WHERE field like CONCAT('%', (SELECT id FROM table2), '%')";

edited May 07 '20 at 09:55

Saurabh Mistry

12,833
5
50
71

answered Jul 08 '16 at 12:38

Leo Barbas

109
1
4

2

Doesn't work if the inner select returns more than one row – Kyle Chadha Nov 08 '17 at 15:18

score 0 · Answer 5 · answered Apr 05 '12 at 04:45

0

Best way would be to create function called NameMatch()

Final Query :

SELECT * FROM mytable  WHERE dbo.NameMatch(name) = 1

The function would look like :

create function dbo.NameMatch 
(@_name varchar(100))
returns bit 
as  begin

    declare @res bit 
    if exists (select 1 from myothertable where @_name like '%' + name + '%' )
     set @res = 1
    else set @res  = 0
    return @res

end

answered Apr 05 '12 at 04:45

Dhananjay

3,673
2
22
20

No - this actually would execute Row By Agonizing Row (RBAR). SQL is SET based... – OMG Ponies Apr 05 '12 at 05:08
You can try running above on MSSQL 2000/2005/2008. It works fine. I just did the same. – Dhananjay Apr 05 '12 at 05:24
"Working" does mean "performs as efficiently as it could". And the question is MySQL, not SQL Server... – OMG Ponies Apr 05 '12 at 05:26

score 0 · Answer 6 · answered Feb 15 '13 at 11:28

0

It Worked FOR ME

SELECT *
FROM mytable
WHERE name
LIKE CONCAT('%',(SELECT name FROM myothertable),'%')

answered Feb 15 '13 at 11:28

Muhammad Haseeb Khan

885
9
24

1

On MySQL 5.6 this query throws Error Code: 1242. Subquery returns more than 1 row. What version did you run this on? – Kingz Dec 16 '13 at 21:15
Dear Make Sure your subquery return only one record. otherwise it will not work – Muhammad Haseeb Khan Dec 20 '13 at 05:26

score 0 · Answer 7 · edited May 15 '20 at 16:43

0

SELECT * 
FROM t1
WHERE t1.name IN (SELECT t2.name FROM t2)
AND (t1.title IN (SELECT t2.title FROM t2)
    OR t1.surname IN (SELECT t2.surname FROM t2))

edited May 15 '20 at 16:43

Nimantha

6,405
6
28
69

answered Aug 16 '13 at 01:51

Bellevue Esaie

1

MYSQL use 'LIKE' in 'WHERE' clause to search in subquery

7 Answers7

Linked