如何在SQL中使用JOIN和LIKE？

Question

我有两个表，假设是表A和表B，并且我想执行一个联接操作，但匹配条件必须是来自A的一列“类似于”B中的一列，这意味着在B中的列之前或之后可以是任何内容：

例如：如果A中的列是'foo'。 那么如果B中的列是'fooblah'，'somethingfooblah'或仅为'foo'，则连接将匹配。 我知道如何在标准的like语句中使用通配符，但在执行联接时感到困惑。 这说得通吗？谢谢。

- jim

Answer 1

使用INSTR函数：

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

使用 LIKE:

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

使用 CONCAT 进行 LIKE 操作：

SELECT *
  FROM TABLE a
  JOIN TABLE b ON b.column LIKE CONCAT('%', a.column ,'%')

请注意，在所有选项中，您可能希望在比较之前将列值转换为大写字母，以确保您获得不考虑大小写敏感性的匹配：

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 plan)输出。

JOIN子句与编写WHERE子句相同。这种JOIN语法也被称为ANSI JOINs，因为它们已经标准化了。非ANSI JOINs看起来像：

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

我不会费力介绍非ANSI的LEFT JOIN示例。使用ANSI JOIN语法的好处是将连接表与实际发生在WHERE子句中的内容分开。

- OMG Ponies

---

LIKE和INSTR哪个在（例如域名）中的速度更快？ - Meloman

在JOIN中遇到了左右两个别名。 - Reihan_amn

hmm使用类似的连接方式可以工作，但会停止使用索引，我已经在列上添加了索引，有什么想法吗？ - Muhammad Omer Aslam

Answer 2

在MySQL中，你可以尝试以下查询：

SELECT * FROM A INNER JOIN B ON B.MYCOL LIKE CONCAT('%', A.MYCOL, '%');

当然，这将是一条非常低效的查询语句，因为它会进行全表扫描。

更新：这里有一个证明

create table A (MYCOL varchar(255));
create table B (MYCOL varchar(255));
insert into A (MYCOL) values ('foo'), ('bar'), ('baz');
insert into B (MYCOL) values ('fooblah'), ('somethingfooblah'), ('foo');
insert into B (MYCOL) values ('barblah'), ('somethingbarblah'), ('bar');
SELECT * FROM A INNER JOIN B ON B.MYCOL LIKE CONCAT('%', A.MYCOL, '%');
+-------+------------------+
| MYCOL | MYCOL            |
+-------+------------------+
| foo   | fooblah          |
| foo   | somethingfooblah |
| foo   | foo              |
| bar   | barblah          |
| bar   | somethingbarblah |
| bar   | bar              |
+-------+------------------+
6 rows in set (0.38 sec)

- Asaph

---

谢谢。我该如何实现相同的功能，但使其更有效率？ - jim

这就是你应该做的。如果你需要更高效，你可以在B表上为MYCOL字段建立索引。 - David Andres

如果您正在使用MyISAM表类型，可以尝试全文索引并查看是否有所帮助。但一般来说，MySQL并不擅长全文搜索。如果全文搜索是您的应用程序的核心部分，请考虑使用类似Apache Lucene的工具 - http://lucene.apache.org/java/docs/ - Asaph

更新：顺便说一下，全文索引需要不同的查询。有关详细信息，请参阅此处的MySQL文档：http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html - Asaph

Answer 3

如果您需要经常这样做...那么您可能希望对A表和B表之间的关系进行非规范化处理。

例如，在向B表插入数据时，您可以基于部分映射向一个连接表写入零个或多个条目，将B映射到A。同样，对任一表的更改都可以更新此关联。

所有这些都取决于A表和B表被修改的频率。如果它们相当静态，则在INSERT上付出的代价比SELECT的重复打击要小。

- David Andres

---

这是一个很好的解决方案，但称其为去规范化并不准确。 - Bill Karwin

可以的，那就称它为连接表吧。 - David Andres

Answer 4

在编写查询时，使用服务器的LIKE或INSTR（或T-SQL中的CHARINDEX）速度太慢，因此我们使用LEFT，如下所示：

select *
from little
left join big
on left( big.key, len(little.key) ) = little.key

我明白这个查询只能使用不同的 结尾，而不是其他建议中的 '%' + b + '%' 来工作，但如果你只需要b+'%', 这已经足够快了。

另一种提高速度（但不节省内存）的方法是在"little"表中创建一个名为"lenkey"的"len(little.key)"列，并在上面的查询中使用它。

- Vitalijs Arkulinskis

---

我知道这并没有回答楼主的问题，但它回答了我的问题。感谢提供示例，因为我的查询匹配了数百万条记录。 - strattonn

Answer 5

在连接操作中使用条件准则与使用Where子句是不同的。表之间的基数可能会导致连接和Where子句之间存在差异。

例如，在Outer Join中使用Like条件将保留连接中列出的第一个表中的所有记录。但是，如果在Where子句中使用相同的条件，则会隐式更改连接为Inner join。通常情况下，记录必须同时存在于两个表中才能完成Where子句中的条件比较。

我通常使用先前答案中给出的样式。

tbl_A as ta
    LEFT OUTER JOIN tbl_B AS tb
            ON ta.[Desc] LIKE '%' + tb.[Desc] + '%'

这样我就可以控制连接类型。

- Geoffrey Fuller