-1

Is there any way for having a like statement in MySQL join, in ON clause? I am having a table called new_product containing product_name generated every day by a cron job and I have an existing product_detail table containing product_name and many different attributes. the name of products are unique in both table but the problem is that sometimes in new_product table I have extra characters appended at the end such as in Milk Fr and in the product detail I have Milk. So I cannot use new_product.product_name = product_detail.product_name I need to have like in ON clause but since I am having 30,000 rows, I am getting an error. I set both these two cols as an index and it didn't help. (new_product.product_name = product_detail.product_name)

Ali
  • 468
  • 1
  • 8
  • 19
  • 2
    add a proper data sample .. please – ScaisEdge Apr 11 '18 at 19:26
  • How is it that you know about like but not where to use it? How is it that the manual or googling did not answer your question? What does "should be fast" mean, why are you saying that & why does googling your actual problem/question not help? Please read [ask] & the downvote arrow mouseover text. Just googling 'fast mysql like join "%"' gives many answers. – philipxy Apr 11 '18 at 19:42
  • Possible duplicate of [Mysql Improve Search Performance with wildcards (%%)](https://stackoverflow.com/q/5905125/3404097) – philipxy Apr 11 '18 at 19:43
  • 1
    Thank you @philipxy it is not duplicate but that was relevant and helpful. – Ali Apr 12 '18 at 21:13

2 Answers2

1

You could use like as you prefer in ON clause eg: 

  select *
  from t1 
  left join t2 on t1.col1 = t2.colx and t1.col3 like 'A%'

or 

  select *
  from t1 
  left join t2 on t1.col1 = concat('%', t2.colx, '%')
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • 2
    I edited my question. Basically, I can't have `=` in `on`clause and the 'concat` solution is slow because I have more than 30,000 rows. – Ali Apr 11 '18 at 21:18
  • 1
    This mean that the use of like in ON clause is perfectly legal .. . – ScaisEdge Apr 12 '18 at 05:55
  • 2
    is there any way to make it run faster other than setting both columns as index? – Ali Apr 12 '18 at 16:00
  • 2
    Not relateted to SQL ..there is not a way to run faster than setting valid index .. a part sql you can improve your server .. or change the logig .. eg : processig the row that match directly faster and the row that need really like as a separated query . – ScaisEdge Apr 12 '18 at 16:17
0

Nothing stops you from having it. If you're concerned about speed. Put an index on the columns

Eric Yang
  • 2,678
  • 1
  • 12
  • 18