mysql select in list with regexp/substring

Question

I have table A:

---CODE-----
|21XDS60020| <-There is somewhere in table B
|21XDS60021|
|21XDS60023| <-There is somewhere in table B
|21XDS60025|
|21XDS60026|

And table B:

----------------DESCRIPTION--------------------------
|FAX21XDS60020[2008_504571][NMS]sdfg bla bla        |
|FAX21XDS52167[2008_XXX324][NMS]sdfg bla blb        |
|FAX21P025P61[2006_501909][21XDS60023]sdfg bla blc  |
|FAX21XDS60025[2006_502499][NMS]sdfg bla bld        |
|FAX21P0251296[2007_503659][NMS]sdfg bla ble        |

---

Expected Result:

---------------------DESCRIPTION--------------------
|FAX21XDS60020[2008_504571][NMS]sdfg bla bla       |
|FAX21P025P61[2006_501909][21XDS60023]sdfg bla blc |

I want to select all 'description' records from table B, if they contain as substring one of the 'code' records of table A I don't know if I can use somehow IN or EXISTS and REGEXP statements in that case.

Something like (of course the following is wrong) :

SELECT description FROM B WHERE description IN (select REGEXP(.*code.*) FROM A);

Answer 1

This is a JOIN operation with a nasty nasty unsargable slow ON condition.

SELECT B.description
  FROM A
  JOIN B ON B.description LIKE CONCAT('%', A.code, '%')

It's slow because 'needle' LIKE '%haystack%' means MySQL has to look at everything in the haystack to find the needle.

On the other hand, 'needle' LIKE 'haystack%' (without the leading %) can use an index. So if this works in your data you should use it.

SELECT B.description
  FROM A
  JOIN B ON B.description LIKE CONCAT('FAX', A.code, '%')

Answer 2

May be a correlated EXISTS something like this:

SELECT description FROM B 
WHERE exists (
    select 1 FROM A
    where B.description like concat('%',A.code,'%') 
);