CODEWITHSUNDEEP
mysqlsql
klochner
klochner

Reputation: 8125

Can you use sql like() functions for table joins?

Assume I have two tables, each with a single string column (name). I'd like to do a left outer join from t1 to t2 where t1.name like %t2.name% or t2.name like %t1.name%

is something like that possible?

Upvotes: 0

Views: 931

Answers (4)

Andomar
Andomar

Reputation: 238246

Pretty much anything that goes in a where clause can also go in an on clause, like:

select  *
from    t1
left join  
        t2
on      t1.name like '%' + t2.name + '%'
        or t2.name like '%' + t1.name + '%'

Upvotes: 3

SWeko
SWeko

Reputation: 30912

As Andomar pointed out, there's not much difference between an on clause and a where clause.

However, using like on a join would be a bad practice in most scenarios, as it can (and will) lead to unexpected results. E.g, if the two tables have rows with names A, Ab, Abc and Abcd, the resulting left join will not have just 4 rows, because it will match basically every row from the first table with every for from the second table, and you'll get 16 results, instead of 4.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

Yes. From the MySQL docs on JOIN Syntax

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause.

Upvotes: 2

dogbane
dogbane

Reputation: 274778

Sure. You can do it like this:

... where t1.name like '%' + t2.name + '%'

Upvotes: 2

Related Questions