在创建具有相互关联的表的数据库时，您最终可能需要检索表之间连接的数据。
您可以使用 MySQL 连接来完成此操作。连接是一种将多个表中的列获取到一组结果中的方法。这通常比尝试执行多个查询并稍后组合它们更有效。
本文着眼于可以在 MySQL 中执行的不同类型的连接，并展示了它们用于组合来自多个表的数据的不同方式。
示例场景
为了进一步了解连接，我们将创建一个简单的杂货数据库，每个商品都有一个类别。类别存储在categories表中，项目存储在单独的items表中。
CREATE TABLE categories (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(250) NOT NULL
);
categories填充数据的示例表：
| ID | 姓名 |
|---|---|
| 1 | 生产 |
| 2 | 熟食店 |
CREATE TABLE items (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(512),
category_id int NULL
);
CREATE TABLE 产品（id int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(255) NOT NULL, image_url varchar(255), category_id INT, KEY category_id_idx (category_id)）；
items填充数据的示例表：
| ID | 姓名 | 类别ID |
|---|---|---|
| 1 | 苹果 | 1 |
| 2 | 奶酪 | 2 |
我们将在整个教程中以这个示例为基础来探索不同类型的连接以及如何使用它们。
内连接
现在我们已经存储了项目和类别，我们可能希望将项目与类别名称一起显示，而不仅仅是category_id，因为“Deli”对人类来说比“2”更有意义。
为此，我们可以使用INNER JOIN，它选择表之间的匹配记录。这是 JOIN 的默认行为，因此INNER JOIN与JOIN.
内部连接的常见错误
警告：如果您在没有ON子句的情况下进行连接，您将执行有时称为 a 的操作CROSS JOIN，这将对右表中的每一行显示左表中的每一行一次。这通常不是我们想要的——它会产生更多的结果。
让我们看一个交叉连接的例子：
-- Don’t do this unless you know what you are doing:
SELECT * FROM items
JOIN categories; -- No ON columns specified!
我们最终得到的结果比我们预想的要多得多！如果有大量数据，这将是一团糟。
| column1 | column2 | column3 |
|---|---|---|
| content1 | content2 | content3 |
| ID | 姓名 | 类别ID | ID | 姓名 |
|---|---|---|---|---|
| 1 | 苹果 | 1 | 1 | 生产 |
| 1 | 苹果 | 1 | 2 | 熟食店 |
| 2 | 奶酪 | 2 | 1 | 生产 |
| 2 | 奶酪 | 2 | 2 | 熟食店 |
指定要加入类别的列
为了得到我们想要的结果，我们必须说出哪些列是相关的。
换句话说，我们不得不说 的主键( id)categories与 的外键( category_id) 相关items。
SELECT * FROM items
-- JOIN is the same as INNER JOIN
JOIN categories ON items.category_id = categories.id;
| ID | 姓名 | 类别ID | ID | 姓名 |
|---|---|---|---|---|
| 1 | 苹果 | 1 | 1 | 生产 |
| 2 | 奶酪 | 2 | 2 | 熟食店 |
现在我们已经方便地返回了每个项目的类别名称！
为列赋予唯一名称
您可能会注意到上表中现在有两个name字段，因为两个表都有自己的name列。为了使查询更有用，我们可以使用别名将列输出为其他内容。
对于此示例，我们将使用cforcategories和ifor items。
SELECT * FROM items AS i -- we now refer to items as i
JOIN categories AS c -- we now refer to categories as c
ON i.category_id = c.id;
注意：使用AS是可选的，所以我们经常会看到它被遗漏了。
指定我们要返回的所有列而不是用 请求所有列也是一个好主意*，尤其是在使用具有许多列的表时，因为这可以使查询运行得更快。在此示例中，让我们省略类别id列。
因为多次选择相同的列名，我们还应该指定这些列来自哪些表。我们可以为此使用i和c别名。
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name -- now refer to categories.name AS category_name
FROM items i
JOIN categories c ON i.category_id = c.id;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 1 | 苹果 | 1 | 生产 |
| 2 | 奶酪 | 2 | 熟食店 |
现在返回一个有用的结果，我们可以显示给用户！
回顾一下，我们使用内部连接通过关系将两个表中的数据组合起来。在我们的示例中，有一个左表——在FROM（在本例中为）之后指定的第一个表，以及在( )items之后指定的右表。JOINcategories
内连接可以用这个维恩图表示，显示返回的唯一数据是项目和类别相关的数据。
左右连接
假设我们向表中添加更多数据：
- 没有类别的项目
- 一个新类别（但还没有使用它的项目）。
categories
| ID | 姓名 |
|---|---|
| 1 | 生产 |
| 2 | 熟食店 |
| 3 | 乳制品 |
items
| ID | 姓名 | 类别ID |
|---|---|---|
| 1 | 苹果 | 1 |
| 2 | 奶酪 | 2 |
| 3 | 面包 | 无效的 |
如果我们INNER JOIN对这些数据进行分析，我们会得到以下信息：
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
JOIN categories c ON i.category_id = c.id;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 1 | 苹果 | 1 | 生产 |
| 2 | 奶酪 | 2 | 熟食店 |
注意到有什么遗漏了吗？
面包不在！为什么不？
当我们对 进行内部连接时i.category_id = c.id，我们告诉 MySQL 只返回具有类别的记录。由于“面包”有一个category_idthat is NULL，它不匹配任何东西，因此不返回。
同样，由于没有项目具有我们新的“乳制品”类别，因此这也不会出现在结果中。
通常，您仍然希望返回所有项目，即使是那些在它所连接的表中没有匹配外键的项目。为此，我们可以使用LEFT JOIN确保返回第一个（左）表中的所有项目记录。RIGHT JOIN工作方式几乎完全相同，只是它返回正确表中的所有记录——在本例中，categories.
如果我们LEFT JOIN对这些数据执行 a ，我们将得到以下信息：
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 1 | 苹果 | 1 | 生产 |
| 2 | 奶酪 | 2 | 熟食店 |
| 3 | 面包 | 无效的 | 无效的 |
现在我们有了所有的项目，这要归功于使用 aLEFT JOIN而不是INNER JOIN!
同样，我们可以使用 aRIGHT JOIN返回所有类别（但不一定是所有项目）。
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 1 | 苹果 | 1 | 生产 |
| 2 | 奶酪 | 2 | 熟食店 |
| 无效的 | 无效的 | 无效的 | 乳制品 |
左连接和右连接可以用这些维恩图表示。
全外连接
如果我们想显示所有项目和所有类别，我们必须进行有时称为 a 的特殊连接FULL OUTER JOIN，尽管 MySQL 不支持这种类型的连接。但是，我们可以通过同时执行aLEFT JOIN和RIGHT JOIN并将它们与 a 组合来模拟这一点UNION。
为此，我们必须添加一个WHERE子句，该子句仅包含来自查询第二部分的NULL项目的记录。id否则，那些具有类别的项目将全部显示两次。
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
UNION ALL
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
-- This prevents duplicate items from showing
-- as we only want categories with no items.
WHERE i.id IS NULL;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 1 | 苹果 | 1 | 生产 |
| 2 | 奶酪 | 2 | 熟食店 |
| 3 | 面包 | 无效的 | 无效的 |
| 无效的 | 无效的 | 无效的 | 乳制品 |
这种类型的“OUTER JOIN”由该图表示。
只显示不相关的数据（WHERE键是NULL）
有时只查询不相关的记录会很有帮助。我们可能只想找到未分类的项目——也许这样我们就可以找到它们来清理它们。
为此，我们可以在 a和中添加一个附加WHERE子句。LEFTRIGHT JOIN
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
WHERE c.id IS NULL;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 3 | 面包 | 无效的 | 无效的 |
此处表示此 JOIN。
要仅显示没有项目的类别，我们可以使用类似RIGHT JOIN的 withWHERE子句，仅显示带有NULL项目的记录id。
SELECT
i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
WHERE i.id IS NULL;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 无效的 | 无效的 | 无效的 | 乳制品 |
此处表示此 JOIN。
仅包含不相关数据的完全外连接
最后，如果我们想同时显示不相关的项目和类别，我们可以使用OUTER JOIN查询类型，但要查找items或categories键NULL。
为了使这个查询工作，它有助于将它的大部分括在括号中并将WHERE子句应用于外部查询。
SELECT * FROM (
SELECT i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
UNION ALL
SELECT i.id,
i.name,
i.category_id,
c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
WHERE i.id IS NULL
) AS all_items_all_categories
WHERE id IS NULL OR category_id IS NULL;
| ID | 姓名 | 类别ID | 分类名称 |
|---|---|---|---|
| 3 | 面包 | 无效的 | 无效的 |
| 无效的 | 无效的 | 无效的 | 乳制品 |
此处表示不相关项的此 JOIN。
总结
您现在应该了解如何使用连接来组合来自多个表的数据以及每种连接类型的不同之处。总结一下：
- INNER JOIN或JOIN仅返回两个表中具有匹配键的记录。
- LEFT JOIN只有当它们也被第二个表引用时，才返回第一个表中的记录。
- RIGHT JOIN仅当它们也被第一个表引用时才从第二个表返回记录。
- FULL OUTER JOIN返回两个表中的所有记录，即使它们在另一个表中没有匹配项。
- WHERE可以过滤连接结果以仅显示带有NULL键的记录。
- UNION可以将两个查询的结果合并到一个结果集中。
对连接有很好的理解，你就可以在 MySQL 中进行强大而高效的查询。
原文标题：Introduction to MySQL joins
原文作者：JD Lien
原文地址：https://planetscale.com/blog/introduction-to-sql-joins
