如何在sql中使用like和join?

2021-12-17 00:00:00 join sql sql-like mysql

我有 2 个表，比如表 A 和表 B，我想执行连接，但匹配条件必须是 A 中的列类似于"B 中的列，这意味着任何东西都可以在之前或之后出现B中的列:

I have 2 tables, say table A and table B and I want to perform a join, but the matching condition has to be where a column from A 'is like' a column from B meaning that anything can come before or after the column in B:

例如:如果 A 中的列是 'foo'.如果 B 中的列是:'fooblah'、'somethingfooblah' 或只是 'foo'，则连接将匹配.我知道如何在标准的 like 语句中使用通配符，但在进行连接时很困惑.这有意义吗?谢谢.

for example: if the column in A is 'foo'. Then the join would match if column in B is either: 'fooblah', 'somethingfooblah', or just 'foo'. I know how to use the wildcards in a standard like statement, but am confused when doing a join. Does this make sense? Thanks.

推荐答案

Using INSTR:

SELECT *
  FROM TABLE a
  JOIN TABLE b ON INSTR(b.column, a.column) > 0

使用喜欢:

SELECT *
  FROM TABLE a
  JOIN TABLE b ON b.column LIKE '%'+ a.column +'%'

将 LIKE 与 CONCAT 一起使用:

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

最有效的最终取决于EXPLAIN计划输出.

The most efficient will depend ultimately on the EXPLAIN plan output.

JOIN 子句与编写 WHERE 子句相同.JOIN 语法也被称为 ANSI JOIN，因为它们是标准化的.非 ANSI JOIN 看起来像:

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

我不会为非 ANSI LEFT JOIN 示例而烦恼.ANSI JOIN 语法的好处在于它将表连接在一起的内容与 WHERE 子句中实际发生的内容分开.

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.

相关文章