MySQL 如何在sql中使用like和join？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1386166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use a like with a join in sql?
提问by OMG Ponies
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:
我有 2 个表，比如表 A 和表 B，我想执行连接，但匹配条件必须是 A 中的列“类似于”B 中的列，这意味着任何东西都可以在列之前或之后出现乙：
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.
例如：如果 A 中的列是 'foo'。如果 B 中的列是：'fooblah'、'somethingfooblah' 或只是 'foo'，则连接将匹配。我知道如何在标准的 like 语句中使用通配符，但在进行连接时很困惑。这有意义吗？谢谢。
回答by OMG Ponies
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:
使用 LIKE 和 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 planoutput.
最有效的最终取决于EXPLAIN 计划的输出。
JOINclauses are identical to writing WHEREclauses. The JOINsyntax is also referred to as ANSI JOINs because they were standardized. Non-ANSI JOINs look like:
JOIN分句与写作WHERE分句相同。该JOIN语法也称为 ANSI JOIN，因为它们是标准化的。非 ANSI JOIN 看起来像：
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 WHEREclause.
我不会打扰非ANSI LEFT JOIN 示例。ANSI JOIN 语法的好处是它将表连接在一起的内容与WHERE子句中实际发生的内容分开。
回答by Asaph
In MySQL you could try:
在 MySQL 中，您可以尝试：
SELECT * FROM A INNER JOIN B ON B.MYCOL LIKE CONCAT('%', A.MYCOL, '%');
SELECT * FROM A INNER JOIN B ON B.MYCOL LIKE CONCAT('%', A.MYCOL, '%');
Of course this would be a massively inefficient query because it would do a full table scan.
当然，这将是一个非常低效的查询，因为它会进行全表扫描。
Update: Here's a proof
更新：这是一个证明
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)
回答by David Andres
If this is something you'll need to do often...then you may want to denormalize the relationship between tables A and B.
如果这是您经常需要做的事情……那么您可能想要对表 A 和 B 之间的关系进行非规范化。
For example, on insert to table B, you could write zero or more entries to a juncion table mapping B to A based on partial mapping. Similarly, changes to either table could update this association.
例如，在插入到表 B 时，您可以将零个或多个条目写入基于部分映射将 B 映射到 A 的联结表。同样，对任一表的更改都可以更新此关联。
This all depends on how frequently tables A and B are modified. If they are fairly static, then taking a hit on INSERT is less painful then repeated hits on SELECT.
这一切都取决于表 A 和 B 的修改频率。如果它们是相当静态的，那么在 INSERT 上点击就没有在 SELECT 上重复点击那么痛苦。
回答by Geoffrey Fuller
Using conditional criteria in a join is definitely different than the Where clause. The cardinality between the tables can create differences between Joins and Where clauses.
在连接中使用条件条件绝对不同于 Where 子句。表之间的基数会在 Joins 和 Where 子句之间产生差异。
For example, using a Like condition in an Outer Join will keep all records in the first table listed in the join. Using the same condition in the Where clause will implicitly change the join to an Inner join. The record has to generally be present in both tables to accomplish the conditional comparison in the Where clause.
例如，在外部联接中使用 Like 条件将保留联接中列出的第一个表中的所有记录。在 Where 子句中使用相同的条件将隐式地将连接更改为内部连接。记录通常必须出现在两个表中才能完成 Where 子句中的条件比较。
I generally use the style given in one of the prior answers.
我通常使用先前答案之一中给出的样式。
tbl_A as ta
LEFT OUTER JOIN tbl_B AS tb
ON ta.[Desc] LIKE '%' + tb.[Desc] + '%'
This way I can control the join type.
这样我就可以控制连接类型。
回答by Vitalijs Arkulinskis
When writing queries with our server LIKE or INSTR (or CHARINDEX in T-SQL) takes too long, so we use LEFT like in the following structure:
当使用我们的服务器 LIKE 或 INSTR（或 T-SQL 中的 CHARINDEX）编写查询时花费的时间太长，因此我们在以下结构中使用 LEFT：
select *
from little
left join big
on left( big.key, len(little.key) ) = little.key
I understand that might only work with varying endingsto the query, unlike other suggestions with '%' + b + '%', but is enough and much faster if you only need b+'%'.
我知道这可能只适用于查询的不同结尾，这与 '%' + b + '%' 的其他建议不同，但是如果您只需要 b+'%' 就足够了，而且速度要快得多。
Another way to optimize it for speed (but not memory) is to create a column in "little" that is "len(little.key)" as "lenkey" and user that instead in the query above.
另一种优化速度（但不是内存）的方法是在“little”中创建一个列，即“len(little.key)”作为“lenkey”，并在上面的查询中创建用户。
