Using INSTR:
SELECT *
FROM TABLE a
JOIN TABLE b ON INSTR(b.column, a.column) > 0
Using LIKE:
SELECT *
FROM TABLE a
JOIN TABLE b ON b.column LIKE '%'+ a.column +'%'
Using LIKE, with CONCAT:
SELECT *
FROM TABLE a
JOIN TABLE b ON b.column LIKE CONCAT('%', a.column ,'%')
Mind that in all options, you'll probably want to drive the column values to uppercase BEFORE comparing to ensure you are getting matches without concern for case sensitivity:
SELECT *
FROM (SELECT UPPER(a.column) 'ua'
TABLE a) a
JOIN (SELECT UPPER(b.column) 'ub'
TABLE b) b ON INSTR(b.ub, a.ua) > 0
The most efficient will depend ultimately on the EXPLAIN plan output.
JOIN
clauses are identical to writing WHERE
clauses. The JOIN
syntax is also referred to as ANSI JOINs because they were standardized. Non-ANSI JOINs look like:
SELECT *
FROM TABLE a,
TABLE b
WHERE INSTR(b.column, a.column) > 0
I'm not going to bother with a Non-ANSI LEFT JOIN example. The benefit of the ANSI JOIN syntax is that it separates what is joining tables together from what is actually happening in the WHERE
clause.