MySQL:连接类型的快速细分

问题描述

我想快速了解一下 MySQL 连接的类型.我知道这些，其余的我不确定它们是什么意思.

I would like a quick breakdown of the types of MySQL joins. I know of these, the rest I am not sure what they mean.

• 逗号分隔(究竟是什么?):SELECT * FROM a, b WHERE b.id = a.beeId AND ...
• 显示来自 a 的信息，即使 b 中没有匹配项:SELECT * FROM a LEFT OUTER JOIN b ON b.id = a.beeId WHERE ...

• comma separated (what exactly is this short for?): SELECT * FROM a, b WHERE b.id = a.beeId AND ...
• show information from a, even if there are no matches in b: SELECT * FROM a LEFT OUTER JOIN b ON b.id = a.beeId WHERE ...

我见过其他连接，但想知道是什么让它们不同，什么是 INNER/OUTER，添加 LEFT 会改变什么.

I have seen other joins, but want to know what makes them different, what is INNER/OUTER, does adding LEFT change things.

我已经知道连接是如何工作的，我只想知道是否还有其他类型的连接，或者它们是否只是获得相同结果的不同方式.

I already know how joins work, I just want to know if there are other types of joins, or if they are just different ways to get the same result.

推荐答案

根据您的评论，最好在 W3Schools<上找到每个方案的简单定义/a>每种类型的第一行对连接类型进行了简要说明

Based on your comment, simple definitions of each is best found at W3Schools The first line of each type gives a brief explanation of the join type

• JOIN:当两个表中至少有一个匹配项时返回行
• LEFT JOIN:返回左表中的所有行，即使右表中没有匹配项
• RIGHT JOIN:返回右表中的所有行，即使左表中没有匹配项
• FULL JOIN:当其中一个表中有匹配项时返回行

结束编辑

简而言之，你给出的逗号分隔的例子

In a nutshell, the comma separated example you gave of

SELECT * FROM a, b WHERE b.id = a.beeId AND ...

从表 a 和 b 中选择每条记录，用逗号分隔表，这也可以用在像

is selecting every record from tables a and b with the commas separating the tables, this can be used also in columns like

SELECT a.beeName,b.* FROM a, b WHERE b.id = a.beeId AND ...

然后在您的示例中 b.id 列和 a.beeId 列匹配的行中获取指示信息.因此，在您的示例中，它将从表 a 和 b 中获取所有信息，其中 b.id 等于 a.beeId.在我的示例中，当 b.id 等于 a.beeId 时，它将从 b 表中获取所有信息，并且仅从 a.beeName 列中获取信息.请注意，还有一个 AND 子句，这将有助于优化您的结果.

It is then getting the instructed information in the row where the b.id column and a.beeId column have a match in your example. So in your example it will get all information from tables a and b where the b.id equals a.beeId. In my example it will get all of the information from the b table and only information from the a.beeName column when the b.id equals the a.beeId. Note that there is an AND clause also, this will help to refine your results.

有关 mySQL 联接和左联接的一些简单教程和解释，请查看 Tizag 的 mySQL 教程.您还可以查看 Keith J. Brown 的网站，了解有关连接的更多信息，这也很不错.

For some simple tutorials and explanations on mySQL joins and left joins have a look at Tizag's mySQL tutorials. You can also check out Keith J. Brown's website for more information on joins that is quite good also.

希望对你有帮助

这篇关于MySQL:连接类型的快速细分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！