MySQL：连接类型的快速细分

Question

提问by Bryan Field

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.

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

comma separated (what exactlyis 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 ...

逗号分隔（这到底是什么意思？）：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 ...

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

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

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.

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

Answer 1

回答by Rufinus

(c) Found on G+ "Data Visualization"

在 G+ 上找到
(c) 在 G+“数据可视化”上找到

or see the following links for a good overview:

或查看以下链接以获得良好的概述：

http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Answer 2

回答by Ryan

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

根据您的评论，最好在W3Schools 上找到每种类型的简单定义。每种类型的第一行简要说明了连接类型

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

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

END EDIT

结束编辑

In a nutshell, the comma separated example you gave of

简而言之，您给出的逗号分隔示例

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

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

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

SELECT a.beeName,b.* FROM a, b WHERE b.id = a.beeId 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.

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

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 websitefor more information on joins that is quite good also.

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

I hope this helps you

我希望这可以帮助你

Answer 3

回答by Andy Balaam

I have 2 tables like this:

我有 2 张这样的表：

> SELECT * FROM table_a;
+------+------+
| id   | name |
+------+------+
|    1 | row1 |
|    2 | row2 |
+------+------+

> SELECT * FROM table_b;
+------+------+------+
| id   | name | aid  |
+------+------+------+
|    3 | row3 |    1 |
|    4 | row4 |    1 |
|    5 | row5 | NULL |
+------+------+------+

INNER JOIN cares about both tables

INNER JOIN 关心两个表

INNER JOIN cares about both tables, so you only get a row if both tables have one. If there is more than one matching pair, you get multiple rows.

INNER JOIN 关心两个表，所以如果两个表都有一个，你只会得到一行。如果有多个匹配对，您会得到多行。

> SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.aid;
+------+------+------+------+------+
| id   | name | id   | name | aid  |
+------+------+------+------+------+
|    1 | row1 |    3 | row3 | 1    |
|    1 | row1 |    4 | row4 | 1    |
+------+------+------+------+------+

It makes no difference to INNER JOIN if you reverse the order, because it cares about both tables:

如果您颠倒顺序，则 INNER JOIN 没有区别，因为它关心两个表：

> SELECT * FROM table_b b INNER JOIN table_a a ON a.id=b.aid;
+------+------+------+------+------+
| id   | name | aid  | id   | name |
+------+------+------+------+------+
|    3 | row3 | 1    |    1 | row1 |
|    4 | row4 | 1    |    1 | row1 |
+------+------+------+------+------+

You get the same rows, but the columns are in a different order because we mentioned the tables in a different order.

您得到相同的行，但列的顺序不同，因为我们提到的表格顺序不同。

LEFT JOIN only cares about the first table

LEFT JOIN 只关心第一张表

LEFT JOIN cares about the first table you give it, and doesn't care much about the second, so you always get the rows from the first table, even if there is no corresponding row in the second:

LEFT JOIN 关心你给它的第一个表，而不太关心第二个，所以你总是从第一个表中获取行，即使第二个表中没有相应的行：

> SELECT * FROM table_a a LEFT JOIN table_b b ON a.id=b.aid;
+------+------+------+------+------+
| id   | name | id   | name | aid  |
+------+------+------+------+------+
|    1 | row1 |    3 | row3 | 1    |
|    1 | row1 |    4 | row4 | 1    |
|    2 | row2 | NULL | NULL | NULL |
+------+------+------+------+------+

Above you can see all rows of table_a even though some of them do not match with anything in table b, but not all rows of table_b - only ones that match something in table_a.

在上面您可以看到 table_a 的所有行，即使其中一些行与 table b 中的任何内容都不匹配，但不是 table_b 的所有行 - 只有与 table_a 中的某些内容匹配的行。

If we reverse the order of the tables, LEFT JOIN behaves differently:

如果我们颠倒表的顺序，LEFT JOIN 的行为会有所不同：

> SELECT * FROM table_b b LEFT JOIN table_a a ON a.id=b.aid;
+------+------+------+------+------+
| id   | name | aid  | id   | name |
+------+------+------+------+------+
|    3 | row3 | 1    |    1 | row1 |
|    4 | row4 | 1    |    1 | row1 |
|    5 | row5 | NULL | NULL | NULL |
+------+------+------+------+------+

Now we get all rows of table_b, but only matching rows of table_a.

现在我们得到了 table_b 的所有行，但只有 table_a 的匹配行。

RIGHT JOIN only cares about the second table

RIGHT JOIN 只关心第二个表

a RIGHT JOIN bgets you exactly the same rows as b LEFT JOIN a. The only difference is the default order of the columns.

a RIGHT JOIN b为您提供与b LEFT JOIN a. 唯一的区别是列的默认顺序。

> SELECT * FROM table_a a RIGHT JOIN table_b b ON a.id=b.aid;
+------+------+------+------+------+
| id   | name | id   | name | aid  |
+------+------+------+------+------+
|    1 | row1 |    3 | row3 | 1    |
|    1 | row1 |    4 | row4 | 1    |
| NULL | NULL |    5 | row5 | NULL |
+------+------+------+------+------+

This is the same rows as table_b LEFT JOIN table_a, which we saw in the LEFT JOIN section.

这与table_b LEFT JOIN table_a我们在 LEFT JOIN 部分中看到的行相同。

Similarly:

相似地：

> SELECT * FROM table_b b RIGHT JOIN table_a a ON a.id=b.aid;
+------+------+------+------+------+
| id   | name | aid  | id   | name |
+------+------+------+------+------+
|    3 | row3 | 1    |    1 | row1 |
|    4 | row4 | 1    |    1 | row1 |
| NULL | NULL | NULL |    2 | row2 |
+------+------+------+------+------+

Is the same rows as table_a LEFT JOIN table_b.

与相同的行table_a LEFT JOIN table_b。

No join at all gives you copies of everything

根本没有加入会为您提供所有内容的副本

If you write your tables with no JOIN clause at all, just separated by commas, you get every row of the first table written next to every row of the second table, in every possible combination:

如果你写的表根本没有 JOIN 子句，只是用逗号分隔，你会得到第一个表的每一行写在第二个表的每一行旁边，在每一个可能的组合中：

> SELECT * FROM table_b b, table_a;
+------+------+------+------+------+
| id   | name | aid  | id   | name |
+------+------+------+------+------+
|    3 | row3 | 1    |    1 | row1 |
|    3 | row3 | 1    |    2 | row2 |
|    4 | row4 | 1    |    1 | row1 |
|    4 | row4 | 1    |    2 | row2 |
|    5 | row5 | NULL |    1 | row1 |
|    5 | row5 | NULL |    2 | row2 |
+------+------+------+------+------+

(This is from my blog post Examples of SQL join types)

（这是来自我的博客文章SQL 连接类型示例）

Answer 4

回答by IBEANS

Full Outer join don't exist in mysql , you might need to use a combination of left and right join.

mysql 中不存在完全外部联接，您可能需要使用左右联接的组合。

MySQL：连接类型的快速细分

提问by Bryan Field

回答by Rufinus

回答by Ryan

回答by Andy Balaam

回答by IBEANS

相关推荐

最近更新

标签

MySQL：连接类型的快速细分

提问by Bryan Field

回答by Rufinus

回答by Ryan

回答by Andy Balaam

回答by IBEANS

相关推荐

使用 NodeJS 在 MySQL 中运行 SQL 文件

MySQL match() against() - 按相关性和列排序？

按日期分组的 MySQL 累计总和

MySQL 如何在mysql的现有表中添加另一个字段

相关推荐

最近更新

标签