【跟着例子学MySQL】连接方式 -- 内连接和外连接

---

前言

举例子，是最简单有效的学习方法。本系列文章以一个贯穿始终的场景，结合多个实例讲解MySQL的基本用法。

❔ 为什么要写这个系列？

• 模仿是最好的老师，实践是检验成果的方法。
• 本系列以实操样例和应用场景为核心，将MySQL基本用法贯穿其中，达到学以致用的效果。

❔ 为什么要学习MySQL？

• MySQL是最常用的数据库之一，具有简单易用的特点，适合初学者学习
• 数据库的基本用法有相通之处，学会MySQL可以为学习其他数据库打下良好基础

❔ 跟别的入门教材有什么不同？

• 以一个贯穿始终的应用场景为主线，渐进地讲解用法
• 难度适中，既有基础方法，也有值得注意的关键细节

❔ 本系列文章不包含哪些内容？

该系列文章的目标是，将最核心的知识，以最直接的方式，呈现给初学者。因此不包含：

• MySQL安装方法
• MySQL系统管理方法，例如备份、恢复、导入导出等
• 高级主题，例如数据库监控、数据库调优和SQL优化

---

回顾

上篇文章👉《【跟着例子学MySQL】SQL进阶 – 视图、事务和变量》 讲了有关视图、事务和变量的用法。这篇介绍有关JOIN的更多用法。

---

INNER JOIN

在两个表的INNER JOIN中，第一个表的每一行都与第二个表的每一行合并（连接）。假设第一个表中有n1行，第二个表中有n2行，INNER JOIN产生n1×n2行的所有组合——它被称为笛卡尔积或交叉积。

例如：

mysql> DROP TABLE IF EXISTS t1, t2;
mysql> CREATE TABLE t1 (
         id INT PRIMARY KEY,
         `desc` VARCHAR(30)
       );
-- `desc` 这是一个保留的词-必须反向引用
mysql> CREATE TABLE t2 (
           id INT PRIMARY KEY,
          `desc` VARCHAR(30)
      );
mysql> INSERT INTO t1 VALUES
         (1, 'ID 1 in t1'),
         (2, 'ID 2 in t1'),
         (3, 'ID 3 in t1');
mysql> INSERT INTO t2 VALUES
         (2, 'ID 2 in t2'),
         (3, 'ID 3 in t2'),
         (4, 'ID 4 in t2');
mysql> SELECT * FROM t1;
+----+------------+
| id | desc       |
+----+------------+
| 1  | ID 1 in t1 |
| 2  | ID 2 in t1 |
| 3  | ID 3 in t1 |
+----+------------+
mysql> SELECT * FROM t2;
+----+------------+
| id | desc       |
+----+------------+
| 2 | ID 2 in t2  |
| 3 | ID 3 in t2  |
| 4 | ID 4 in t2  |
+----+------------+
mysql> SELECT *
FROM t1 INNER JOIN t2;
+----+------------+----+------------+
| id | desc       | id | desc       |
+----+------------+----+------------+
| 1  | ID 1 in t1 | 2  | ID 2 in t2 |
| 2  | ID 2 in t1 | 2  | ID 2 in t2 |
| 3  | ID 3 in t1 | 2  | ID 2 in t2 |
| 1  | ID 1 in t1 | 3  | ID 3 in t2 |
| 2  | ID 2 in t1 | 3  | ID 3 in t2 |
| 3  | ID 3 in t1 | 3  | ID 3 in t2 |
| 1  | ID 1 in t1 | 4  | ID 4 in t2 |
| 2  | ID 2 in t1 | 4  | ID 4 in t2 |
| 3  | ID 3 in t1 | 4  | ID 4 in t2 |
+----+------------+----+------------+
-- 查询t1和t2 (*)的所有列 
-- 内部连接产生t1和t2中的行的所有组合

你可以通过使用ON子句来施加约束，例如：

mysql> SELECT *
FROM t1 INNER JOIN t2 ON t1.id = t2.id;
+----+------------+----+------------+
| id | desc       | id | desc       |
+----+------------+----+------------+
| 2  | ID 2 in t1 | 2  | ID 2 in t2 |
| 3  | ID 3 in t1 | 3  | ID 3 in t2 |
+----+------------+----+------------+

请注意，以下内容是等效的：

mysql> SELECT *
FROM t1 INNER JOIN t2 ON t1.id = t2.id;
mysql> SELECT *
FROM t1 JOIN t2 ON t1.id = t2.id; -- 默认的连接是内部的连接
mysql> SELECT *
FROM t1 CROSS JOIN t2 ON t1.id = t2.id; -- 也称为交叉连接
--如果连接列具有相同的名称，则可以使用USING子句
mysql> SELECT * FROM t1 INNER JOIN t2 USING (id);
+----+------------+------------+
| id | desc       | desc       |
+----+------------+------------+
| 2  | ID 2 in t1 | ID 2 in t2 |
| 3  | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+
-- 结果集中只有3列，而不是带有ON子句的4列
mysql> SELECT *
       FROM t1 INNER JOIN t2 WHERE t1.id = t2.id; -- 使用Where代替ON
mysql> SELECT *
       FROM t1, t2 WHERE t1.id = t2.id; -- 使用 "," 操作符执行Join

---

OUTER JOIN

带约束（ON or USING）的INNER JOIN产生在两个表中找到的行。另一方面，OUTER JOIN可以生成位于一个表中但不在另一个表中的行。有两种外部连接： LEFT JOIN生成的行在左表中，但可能不在右表中；而RIGHT JOIN生成的行在右表中，但可能不在左表中。

在左连接中，当左表中的一行与右表不匹配时，它仍然被选择，但通过与右表中的“假”记录NULL相结合。

mysql> SELECT *
       FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
+----+------------+------+------------+
| id | desc       | id   | desc       |
+----+------------+------+------------+
| 1  | ID 1 in t1 | NULL | NULL       |
| 2  | ID 2 in t1 | 2    | ID 2 in t2 |
| 3  | ID 3 in t1 | 3    | ID 3 in t2 |
+----+------------+------+------------+
mysql> SELECT *
       FROM t1 LEFT JOIN t2 USING (id);
+----+------------+------------+
| id | desc       | desc       |
+----+------------+------------+
| 1  | ID 1 in t1 | NULL       |
| 2  | ID 2 in t1 | ID 2 in t2 |
| 3  | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+
mysql> SELECT *
       FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
+------+------------+----+------------+
| id   | desc       | id | desc       |
+------+------------+----+------------+
| 2    | ID 2 in t1 | 2 | ID 2 in t2  |
| 3    | ID 3 in t1 | 3 | ID 3 in t2  |
| NULL | NULL       | 4 | ID 4 in t2  |
+------+------------+----+------------+
mysql> SELECT *
       FROM t1 RIGHT JOIN t2 USING (id);
+----+------------+------------+
| id | desc       | desc       |
+----+------------+------------+
| 2  | ID 2 in t2 | ID 2 in t1 |
| 3  | ID 3 in t2 | ID 3 in t1 |
| 4  | ID 4 in t2 | NULL       |
+----+------------+------------+

因此，LEFT JOIN将确保结果集包含左表中的每一行。这一点很重要，因为在一些查询中，你有兴趣搜索那些在左表中存在，而在右表中不存在的行，例如，搜索没有供应商的项目。例如：

mysql> SELECT t1.id, t1.desc
         FROM t1 LEFT JOIN t2 USING (id)
         WHERE t2.id IS NULL;
+----+------------+
| id | desc       |
+----+------------+
| 1  | ID 1 in t1 |
+----+------------+

请注意，以下是等效的：

mysql> SELECT *
       FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
mysql> SELECT *
       FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;
mysql> SELECT *
       FROM t1 LEFT JOIN t2 USING (id); -- Join列相同
+----+------------+------------+
| id | desc       | desc       |
+----+------------+------------+
| 1  | ID 1 in t1 | NULL       |
| 2  | ID 2 in t1 | ID 2 in t2 |
| 3  | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+
-- `OUTER JOIN`不能使用`WHERE`
mysql> SELECT *
       FROM t1 LEFT JOIN t2 WHERE t1.id = t2.id;
ERROR 1064 (42000): You have an error in your SQL syntax;

---

未完待续

MySQL所有基础用法已经介绍完毕，下一篇讲解一个综合练习。

---

如果喜欢这篇文章，请不要忘记关注🧡、点赞👍和收藏📔哦！