外观
什么是关联查询
关联查询（Join Query）在SQL中是指将两个或多个表中的数据行根据某些共同的列进行组合，以形成一个新的结果集。 这种查询允许你从多个表中检索信息，而这些表通常通过某种逻辑关系相连，比如主键-外键关系。关联查询是关系型数据库管理系统（RDBMS）中用于处理和分析跨多表数据的核心功能之一。
- 基于条件组合数据： 关联查询使用一个或多个条件来决定哪些行应该被组合在一起。这个条件通常是两个表中某一列值相等，但这并不限于等于操作，也可以是其他类型的比较或逻辑表达式。
- 创建临时结果集： 当执行关联查询时，数据库会创建一个临时的结果集，它包含了来自参与关联的所有表的相关数据。这个结果集可以进一步被过滤、排序或分组。
- 多种连接类型： SQL支持不同的JOIN类型，如INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN等，每种类型决定了当没有匹配的行时如何处理结果集。例如，INNER JOIN只会返回有匹配的数据行，而LEFT JOIN会保留左表的所有行，即使右表中没有匹配项。
- 提高数据检索效率： 通过正确地使用索引和优化JOIN条件，关联查询可以帮助提高数据检索的速度和效率。这是因为在实际应用中，数据往往分散存储在多个表中，有效地关联这些表能够减少数据冗余并提高查询性能。
- 遵循关系模型原则： 关联查询的设计和实现紧密遵循关系数据库理论中的规范化原则，确保数据的一致性和完整性。同时，它也体现了关系型数据库的一个重要特性：能够在保持数据独立性的同时，灵活地访问和操作数据。
直接实操，演示用的数据库和表：
数据库名称为： project_test
表 course：
bash
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(255) | YES | | NULL | |
| outline | varchar(255) | YES | | NULL | |
| hour | varchar(10) | NO | | 0 | |
| classroom | varchar(255) | YES | | NULL | |
| remark | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+数据
bash
+----+--------------------+--------------------------+------+--------------------------------+-----------------+
| id | title | outline | hour | classroom | remark |
+----+--------------------+--------------------------+------+--------------------------------+-----------------+
| 1 | 高数 | 简介12332 | 100 | 阶梯教室201 | 备注 |
| 2 | c语言 | c语言设计 | 70 | 大课堂8811 | 自带机器 |
| 3 | 程控交换技术 | 程控交换技术简介 | 100 | 通讯工程学院实验室203 | 不会的别来 |
| 4 | c语言进阶 | c语言程序进阶 | 50 | 大课堂8811 | 备注 |
| 5 | 光通信 | 阿巴阿巴 | 80 | 通讯工程学院实验室206 | 阿巴 |
| 6 | 水课 | 来划水 | 20 | 宿舍 | 水课啊 |
+----+--------------------+--------------------------+------+--------------------------------+-----------------+teacher
bash
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| tel | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| teacher_id | int(11) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+bash
+----+-----------+------+-----------------------+------------+
| id | name | tel | title | teacher_id |
+----+-----------+------+-----------------------+------------+
| 1 | 张老师 | 101 | 博士生导师 | 101 |
| 2 | 李老师 | 102 | 研究生导师 | 102 |
| 3 | 白老师 | 103 | 科学院院士 | 103 |
| 4 | 华老师 | 104 | 诺贝尔奖获得者 | 104 |
+----+-----------+------+-----------------------+------------+student
bash
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| student_id | varchar(200) | NO | | NULL | |
| sex | varchar(255) | NO | | NULL | |
| profession | varchar(255) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+bash
+----+--------+------------+-----+--------------------------+
| id | name | student_id | sex | profession |
+----+--------+------------+-----+--------------------------+
| 1 | 张三 | s001 | 男 | 通信工程 |
| 2 | 李四 | s002 | 男 | 通信工程 |
| 3 | 王五 | s003 | 男 | 通信工程 |
| 4 | 刘六 | s004 | 女 | 通信工程 |
| 5 | 毛七 | s005 | 女 | 通信工程 |
| 6 | 黑八 | s006 | 男 | 计算机科学与技术 |
| 7 | 黄九 | s007 | 女 | 数学 |
| 8 | 满十 | s008 | 男 | 英语 |
+----+--------+------------+-----+--------------------------+关联表 teacher_course：
bash
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| course_id | int(11) | NO | | NULL | |
| teacher_id | int(11) | NO | | NULL | |
+------------+---------+------+-----+---------+----------------+bash
+----+-----------+------------+
| id | course_id | teacher_id |
+----+-----------+------------+
| 1 | 1 | 3 |
| 2 | 3 | 3 |
| 3 | 2 | 2 |
| 4 | 4 | 2 |
| 5 | 5 | 4 |
+----+-----------+------------+关联表 course_student：
bash
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sudent_id | int(11) | NO | | NULL | |
| course_id | int(11) | NO | | NULL | |
+-----------+---------+------+-----+---------+----------------+bash
+----+-----------+-----------+
| id | sudent_id | course_id |
+----+-----------+-----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 4 |
| 4 | 2 | 3 |
| 5 | 2 | 5 |
| 6 | 2 | 1 |
| 7 | 3 | 1 |
| 8 | 3 | 3 |
| 9 | 3 | 5 |
| 10 | 4 | 1 |
| 11 | 4 | 3 |
| 12 | 4 | 5 |
| 13 | 5 | 1 |
| 14 | 5 | 3 |
| 15 | 5 | 5 |
| 16 | 6 | 1 |
| 17 | 6 | 2 |
| 18 | 6 | 4 |
| 19 | 7 | 1 |
| 20 | 7 | 4 |
+----+-----------+-----------+准备工作完成！！
INNER JOIN (内连接)
只返回n个表中满足连接条件的匹配行(简述：关联表里有多少满足条件的关联项，就会查出多少条对应数据)，下面我们使用 INNER JOIN 来连接查询 老师对应的课程 和 教学所在教室：
- 只返回匹配的行： INNER JOIN 仅返回满足连接条件的行，即左表和右表中都存在匹配键值的行。不匹配的行将被排除在结果集之外。
- 基于连接条件： INNER JOIN 需要一个 ON 子句来指定连接条件。这个条件通常是比较两个表中某一列的值（如主键和外键），但也可以是任何逻辑表达式。
- 高效的查询性能： 由于 INNER JOIN 只处理匹配的行，因此它可以比其他类型的连接更高效，尤其是在处理大型数据集时。适当的索引可以进一步提高 INNER JOIN 的性能。
- 广泛使用： INNER JOIN 是最常见的连接类型，适用于大多数需要从多个表中检索相关数据的场景。它确保了结果集中只包含有效的、相互关联的数据。
- 对称性： INNER JOIN 对左右表的位置不敏感，即 A INNER JOIN B 和 B INNER JOIN A 会产生相同的结果集，前提是连接条件相同。
sql
SELECT a.name, c.title, c.classroom
FROM teacher a
INNER JOIN teacher_course ac
ON a.id = ac.teacher_id
INNER JOIN course c
ON ac.course_id = c.idbash
+-----------+--------------------+--------------------------------+
| name | title | classroom |
+-----------+--------------------+--------------------------------+
| 白老师 | 高数 | 阶梯教室201 |
| 白老师 | 程控交换技术 | 通讯工程学院实验室203 |
| 李老师 | c语言 | 大课堂8811 |
| 李老师 | c语言进阶 | 大课堂8811 |
| 华老师 | 光通信 | 通讯工程学院实验室206 |
+-----------+--------------------+--------------------------------+这里只显示有关联课程的老师，如果没有关联的课程（比如张老师），则不会显示。
LEFT JOIN (左连接) 也叫左外连接（left [outer] join）
返回左表中的所有记录，即使右表中没有匹配。如果右表中没有匹配，则结果为NULL。
- 保留左表的所有行： LEFT JOIN 会确保左表中的每一行都出现在结果集中，即使右表中没有相应的匹配行。对于那些在右表中找不到匹配项的左表行，右表的列将被填充为 NULL。
- 基于连接条件： LEFT JOIN 需要一个 ON 子句来指定连接条件。这个条件决定了哪些行被认为是“匹配”的，并且只有这些匹配的行会在结果集中共享数据。
- 部分 NULL 值： 如果左表中的一行在右表中没有找到匹配项，那么结果集中该行对应的右表列将包含 NULL 值。这有助于识别那些在右表中没有对应记录的左表行。
- 对称性： LEFT JOIN 和 RIGHT JOIN 是对称的操作。你可以通过交换两个表的位置并使用 RIGHT JOIN 来实现相同的结果。例如，A LEFT JOIN B 等效于 B RIGHT JOIN A。
- 广泛使用： LEFT JOIN 是一种非常常见的连接类型，适用于许多需要从多个表中检索相关数据的场景，特别是当你希望保留左表中的所有记录时。
sql
SELECT a.name, c.title, c.classroom
FROM teacher a
LEFT JOIN teacher_course ac
ON a.id = ac.teacher_id
LEFT JOIN course c
ON ac.course_id = c.idbash
+-----------+--------------------+--------------------------------+
| name | title | classroom |
+-----------+--------------------+--------------------------------+
| 白老师 | 高数 | 阶梯教室201 |
| 白老师 | 程控交换技术 | 通讯工程学院实验室203 |
| 李老师 | c语言 | 大课堂8811 |
| 李老师 | c语言进阶 | 大课堂8811 |
| 华老师 | 光通信 | 通讯工程学院实验室206 |
| 张老师 | NULL | NULL |
+-----------+--------------------+--------------------------------+这里张老师没有对应的课程，所以没有对应的记录。
查询一下 某个学生的所有课程信息：
sql
SELECT a.name, a.profession, c.title, c.classroom
FROM student a
LEFT JOIN course_student
ON a.id = course_student.sudent_id
LEFT JOIN course c
ON course_student.course_id = c.id
WHERE a.name = '张三'bash
+--------+--------------+---------------+-----------------+
| name | profession | title | classroom |
+--------+--------------+---------------+-----------------+
| 张三 | 通信工程 | 高数 | 阶梯教室201 |
| 张三 | 通信工程 | c语言 | 大课堂8811 |
| 张三 | 通信工程 | c语言进阶 | 大课堂8811 |
+--------+--------------+---------------+-----------------+会查询 左表（student）中在 右表（course_student） 中有对应记录的记录，如果没有，则返回NULL。
RIGHT JOIN (右连接) 也叫右外连接（right [outer] join）
返回右表中的所有记录，即使左表中没有匹配。如果左表中没有匹配，则结果为NULL。
- 保留右表的所有行： RIGHT JOIN 会确保右表中的每一行都出现在结果集中，即使左表中没有相应的匹配行。对于那些在左表中找不到匹配项的右表行，左表的列将被填充为 NULL。
- 基于连接条件： RIGHT JOIN 需要一个 ON 子句来指定连接条件。这个条件决定了哪些行被认为是“匹配”的，并且只有这些匹配的行会在结果集中共享数据。
- 对称性： RIGHT JOIN 和 LEFT JOIN 是对称的操作。你可以通过交换两个表的位置并使用 LEFT JOIN 来实现相同的结果。例如，A RIGHT JOIN B 等效于 B LEFT JOIN A。
- 可能产生部分 NULL 值： 如果右表中的一行在左表中没有找到匹配项，那么结果集中该行对应的左表列将包含 NULL 值。这有助于识别那些在左表中没有对应记录的右表行。
- 并非总是必要的： 在某些情况下，RIGHT JOIN 可能不是最直观的选择。由于它可以简单地通过交换表顺序并使用 LEFT JOIN 来替代，因此很多开发者更倾向于使用 LEFT JOIN，因为它通常更容易理解和维护。
sql
SELECT a.name, c.title, c.classroom
FROM teacher a
RIGHT JOIN teacher_course tc
ON a.id = tc.teacher_id
RIGHT JOIN course c
ON tc.course_id = c.idbash
+-----------+--------------------+--------------------------------+
| name | title | classroom |
+-----------+--------------------+--------------------------------+
| 白老师 | 高数 | 阶梯教室201 |
| 李老师 | c语言 | 大课堂8811 |
| 白老师 | 程控交换技术 | 通讯工程学院实验室203 |
| 李老师 | c语言进阶 | 大课堂8811 |
| 华老师 | 光通信 | 通讯工程学院实验室206 |
| NULL | 水课 | 宿舍 |
+-----------+--------------------+--------------------------------+查询某个课程有多少学生报名：
sql
SELECT a.name, a.profession, c.title, c.classroom
FROM student a
RIGHT JOIN course_student cs
ON a.id = cs.sudent_id
RIGHT JOIN course c
ON cs.course_id = c.id
WHERE c.title = '水课'bash
+------+------------+--------+-----------+
| name | profession | title | classroom |
+------+------------+--------+-----------+
| NULL | NULL | 水课 | 宿舍 |
+------+------------+--------+-----------+这里也可以用 LEFT JOIN 来实现，效果是一样的。
FULL JOIN (全连接) 也叫满连接（full [outer] join）
返回两个表中的所有记录，当没有匹配时，结果集中不匹配的那一侧将包含NULL （mysql 不直接支持。。。服啦）
- 保留所有行： FULL JOIN 会返回两个表中的所有记录，无论它们是否在另一个表中有匹配项。如果一个表中的一行在另一个表中没有找到匹配项，那么结果集中该行对应的另一表的列将填充 NULL。
- 结合 LEFT JOIN 和 RIGHT JOIN： 从某种意义上说，FULL JOIN 是 LEFT JOIN 和 RIGHT JOIN 的组合。它不仅包括左表的所有行（如 LEFT JOIN），还包括右表的所有行（如 RIGHT JOIN），并且对于两边都没有匹配的情况，使用 NULL 补充。
- 需要明确的连接条件： 尽管 FULL JOIN 会返回所有行，但通常你仍然需要提供一个 ON 子句来指定连接条件。这个条件决定了哪些行被认为是“匹配”的，并且只有这些匹配的行会在结果集中共享数据。
- 可能产生较大的结果集： 由于 FULL JOIN 包含了两个表中的所有行，因此结果集可能会非常大，尤其是在处理大型数据集时。这可能会对性能产生影响，因此在设计查询时应考虑这一点。
- 并非所有数据库都支持： 并不是所有的数据库系统都直接支持 FULL JOIN。例如，MySQL 不直接支持 FULL JOIN，但在某些情况下可以通过 UNION 操作结合 LEFT JOIN 和 RIGHT JOIN 来模拟 FULL JOIN 的效果。
Websites 表：
bash
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+access_log 网站访问记录表:
bash
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)sql
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;FULL OUTER JOIN 关键字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中没有匹配或者 "access_log" 表中的行在 "Websites" 表中没有匹配，也会列出这些行;
mysql 不能 full outer join，所以我们另聘蹊径：
sql
SELECT s.name, s.profession, c.title, c.classroom
FROM student s
LEFT JOIN course_student cs ON s.id = cs.sudent_id
LEFT JOIN course c ON cs.course_id = c.id
UNION
SELECT s.name, s.profession, c.title, c.classroom
FROM student s
RIGHT JOIN course_student cs ON s.id = cs.sudent_id
RIGHT JOIN course c ON cs.course_id = c.id
WHERE s.id IS NULL;bash
+--------+--------------------------+--------------------+--------------------------------+
| name | profession | title | classroom |
+--------+--------------------------+--------------------+--------------------------------+
| 张三 | 通信工程 | 高数 | 阶梯教室201 |
| 张三 | 通信工程 | c语言 | 大课堂8811 |
| 张三 | 通信工程 | c语言进阶 | 大课堂8811 |
| 李四 | 通信工程 | 程控交换技术 | 通讯工程学院实验室203 |
| 李四 | 通信工程 | 光通信 | 通讯工程学院实验室206 |
| 李四 | 通信工程 | 高数 | 阶梯教室201 |
| 王五 | 通信工程 | 高数 | 阶梯教室201 |
| 王五 | 通信工程 | 程控交换技术 | 通讯工程学院实验室203 |
| 王五 | 通信工程 | 光通信 | 通讯工程学院实验室206 |
| 刘六 | 通信工程 | 高数 | 阶梯教室201 |
| 刘六 | 通信工程 | 程控交换技术 | 通讯工程学院实验室203 |
| 刘六 | 通信工程 | 光通信 | 通讯工程学院实验室206 |
| 毛七 | 通信工程 | 高数 | 阶梯教室201 |
| 毛七 | 通信工程 | 程控交换技术 | 通讯工程学院实验室203 |
| 毛七 | 通信工程 | 光通信 | 通讯工程学院实验室206 |
| 黑八 | 计算机科学与技术 | 高数 | 阶梯教室201 |
| 黑八 | 计算机科学与技术 | c语言 | 大课堂8811 |
| 黑八 | 计算机科学与技术 | c语言进阶 | 大课堂8811 |
| 黄九 | 数学 | 高数 | 阶梯教室201 |
| 黄九 | 数学 | c语言进阶 | 大课堂8811 |
| 满十 | 英语 | NULL | NULL |
| NULL | NULL | 水课 | 宿舍 |
+--------+--------------------------+--------------------+--------------------------------+
22 rows in set (0.02 sec)SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。它可以从多个表中选择数据，并将结果集组合成一个结果集。使用 UNION 时，每个 SELECT 语句必须具有相同数量的列，且对应列的数据类型必须相似。
CROSS JOIN 交叉连接
不带ON子句的CROSS JOIN会产生笛卡尔积，即左表的每一行与右表的每一行配对。如果加上ON子句，则等同于INNER JOIN。
- 产生笛卡尔积： CROSS JOIN 会生成两个表中所有行的组合。例如，如果表 A 有 3 行，表 B 有 4 行，那么 CROSS JOIN 的结果将包含 12 (3 x 4) 行。
- 不带 ON 子句： CROSS JOIN 不需要 ON 子句来指定连接条件，因为它本质上是无条件地将两个表的所有行进行组合。
- 等同于没有 WHERE 条件的内连接： 在某些数据库系统中，CROSS JOIN 可以通过省略 ON 子句的 JOIN 来实现。例如，SELECT * FROM table1 CROSS JOIN table2; 与 SELECT * FROM table1, table2; 是等效的。
- 可能导致大数据量： 如果两个表的行数都很大，CROSS JOIN 可能会导致非常大的结果集，这可能会对性能产生负面影响，并且在实际应用中很少直接使用。
- 用于特定场景： 虽然 CROSS JOIN 通常不是最常用的连接类型，但在某些情况下它是有用的，比如当你需要为每个记录创建一个固定数量的副本，或者当你确实需要所有可能的组合时。
先演示一下 不带 on 的：
sql
SELECT t.`name`, c.title
FROM teacher t CROSS JOIN course cbash
+-----------+--------------------+
| name | title |
+-----------+--------------------+
| 张老师 | 高数 |
| 李老师 | 高数 |
| 白老师 | 高数 |
| 华老师 | 高数 |
| 张老师 | c语言 |
| 李老师 | c语言 |
| 白老师 | c语言 |
| 华老师 | c语言 |
| 张老师 | 程控交换技术 |
| 李老师 | 程控交换技术 |
| 白老师 | 程控交换技术 |
| 华老师 | 程控交换技术 |
| 张老师 | c语言进阶 |
| 李老师 | c语言进阶 |
| 白老师 | c语言进阶 |
| 华老师 | c语言进阶 |
| 张老师 | 光通信 |
| 李老师 | 光通信 |
| 白老师 | 光通信 |
| 华老师 | 光通信 |
| 张老师 | 水课 |
| 李老师 | 水课 |
| 白老师 | 水课 |
| 华老师 | 水课 |
+-----------+--------------------+
24 rows in set (0.01 sec)注：笛卡尔积；直白点就是把左表和右表都列出来，然后两两配对，比如左表有3行，右表有5行，那么就生成15行。
下面用一下 带 ON 的：
sql
SELECT t.`name`, c.title, c.classroom
FROM teacher t
CROSS JOIN teacher_course tc
ON t.id = tc.teacher_id
CROSS JOIN course c
ON c.id = tc.course_idbash
+-----------+--------------------+--------------------------------+
| name | title | classroom |
+-----------+--------------------+--------------------------------+
| 白老师 | 高数 | 阶梯教室201 |
| 白老师 | 程控交换技术 | 通讯工程学院实验室203 |
| 李老师 | c语言 | 大课堂8811 |
| 李老师 | c语言进阶 | 大课堂8811 |
| 华老师 | 光通信 | 通讯工程学院实验室206 |
+-----------+--------------------+--------------------------------+
5 rows in set (0.02 sec)可见，和 INNER JOIN 一样
SELF JOIN 自连接
是一个表与它自身进行的连接，通常用于查找具有层次结构的数据，如组织结构或员工管理链。 在 SQL 中，自连接是一种特殊的 JOIN 操作，它允许一个表与它自身的一个或多个字段进行比较，从而实现对数据的分组和聚合。
- 同一个表的两次引用： 在 SELF JOIN 中，同一个表会被视为两个不同的实体来参与连接。为了区分这两个实体，通常会给表起别名（alias），以便可以在查询中分别引用它们。
- 用于表示层次关系： SELF JOIN 常用于表示一对多的关系，其中每个记录可以有一个父记录（或上级记录）。例如，在员工表中，每个员工可以有一个上级经理，而经理本身也是该表中的一名员工。
- 连接条件： 连接条件通常是基于一个外键字段，这个字段指向表中的另一个主键字段。例如，在员工表中，manager_id 可能是指向 employee_id 的外键。
- 避免重复列名冲突： 由于是在同一个表上进行连接，可能会出现列名冲突的情况。为了避免这种情况，使用表别名和列别名可以帮助清晰地指定每个列的来源。
- 递归层次结构： 对于更复杂的层次结构，如无限层级的分类或组织结构，可能需要使用递归查询（如SQL标准中的 WITH RECURSIVE）来遍历整个层次结构。
表结构：
bash
+-------------+--------------+------+-----+----------------------+--------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+----------------------+--------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| createTime | datetime(6) | NO | | CURRENT_TIMESTAMP(6) | |
| updateTime | datetime(6) | NO | | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| type | int(11) | NO | | NULL | |
| status | int(11) | NO | | 0 | |
| title | varchar(50) | NO | | NULL | |
| value | varchar(50) | NO | | NULL | |
| parentId | int(11) | NO | | 0 | |
| description | varchar(255) | YES | | NULL | |
| sort | int(11) | YES | | 0 | |
+-------------+--------------+------+-----+----------------------+--------------------------------+基本数据：
bash
+----+----------------------------+----------------------------+------+--------+--------------------+-------------------------+----------+-----------------+------+
| id | createTime | updateTime | type | status | title | value | parentId | description | sort |
+----+----------------------------+----------------------------+------+--------+--------------------+-------------------------+----------+-----------------+------+
| 1 | 2024-03-29 11:27:28.073025 | 2024-03-29 11:27:43.499535 | 0 | 0 | 行业 | industry | 0 | 行业字典项 | 0 |
| 2 | 2024-03-29 11:32:53.154392 | 2024-03-29 11:32:53.154392 | 0 | 0 | 职位 | position | 0 | NULL | 0 |
| 3 | 2024-03-29 11:33:34.892866 | 2024-03-29 11:33:34.892866 | 0 | 0 | 规模 | scale | 0 | NULL | 0 |
| 4 | 2024-03-29 11:34:57.600602 | 2024-03-29 11:34:57.600602 | 0 | 0 | 专委会 | special | 0 | NULL | 0 |
| 5 | 2024-04-01 09:59:23.798674 | 2024-04-01 09:59:23.798674 | 0 | 0 | 制造业 | manufacturing | 1 | NULL | 0 |
| 6 | 2024-04-01 09:59:44.634897 | 2024-04-01 09:59:44.634897 | 0 | 0 | 交运物流 | logistics | 1 | NULL | 0 |
| 7 | 2024-04-01 10:14:00.135731 | 2024-04-01 10:14:00.135731 | 0 | 0 | 通讯行业 | communication | 1 | NULL | 0 |
| 8 | 2024-04-01 10:14:50.871233 | 2024-04-01 10:14:50.871233 | 0 | 0 | 超大型 | Extra large | 3 | NULL | 0 |
| 9 | 2024-04-01 10:15:00.389472 | 2024-04-01 10:15:00.389472 | 0 | 0 | 大型 | large | 3 | NULL | 0 |
| 10 | 2024-04-01 10:15:16.022855 | 2024-04-01 10:15:16.022855 | 0 | 0 | 中型 | medium size | 3 | NULL | 0 |
| 11 | 2024-04-01 10:15:29.581758 | 2024-04-01 10:15:29.581758 | 0 | 0 | 小型 | small | 3 | NULL | 0 |
| 12 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 微型 | miniature | 3 | NULL | 0 |
| 13 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 会长 | President | 2 | NULL | 0 |
| 14 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 名誉会长 | Honorary President | 2 | NULL | 0 |
| 15 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 副会长 | Vice president | 2 | NULL | 0 |
| 16 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 理事 | director | 2 | NULL | 0 |
| 17 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 会员 | member | 2 | NULL | 0 |
| 18 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 智能制造 | intelligent | 4 | NULL | 0 |
| 19 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 生物医药 | biology | 4 | NULL | 0 |
| 20 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 金融投资 | finance | 4 | NULL | 0 |
| 21 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 轨道交通 | track | 4 | NULL | 0 |
| 22 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:28:06.907779 | 0 | 0 | 建设行业 | construction | 4 | NULL | 0 |
| 23 | 2024-04-15 19:37:03.758482 | 2024-05-13 20:50:44.665558 | 1 | 0 | 圈子分类 | FriendsClassify | 0 | NULL | 0 |
| 24 | 2024-04-15 19:37:44.916770 | 2024-05-13 20:50:49.870535 | 1 | 0 | 业务资源对接 | BusinessResourceDocking | 23 | NULL | 0 |
| 25 | 2024-04-15 19:38:00.025783 | 2024-05-13 20:50:52.175268 | 1 | 0 | 人才招聘 | Recruitment | 23 | NULL | 0 |
+----+----------------------------+----------------------------+------+--------+--------------------+-------------------------+----------+-----------------+------+
25 rows in set (0.00 sec)下面使用自连接，不附加任何条件，查询全部数据：
sql
select *
FROM qinghe_dictionary a
JOIN qinghe_dictionary b
ON a.id = b.parentIdbash
+----+----------------------------+----------------------------+------+--------+--------------+-----------------+----------+-----------------+------+----+----------------------------+----------------------------+------+--------+--------------------+-------------------------+----------+-------------+------+
| id | createTime | updateTime | type | status | title | value | parentId | description | sort | id | createTime | updateTime | type | status | title | value | parentId | description | sort |
+----+----------------------------+----------------------------+------+--------+--------------+-----------------+----------+-----------------+------+----+----------------------------+----------------------------+------+--------+--------------------+-------------------------+----------+-------------+------+
| 1 | 2024-03-29 11:27:28.073025 | 2024-03-29 11:27:43.499535 | 0 | 0 | 行业 | industry | 0 | 行业字典项 | 0 | 5 | 2024-04-01 09:59:23.798674 | 2024-04-01 09:59:23.798674 | 0 | 0 | 制造业 | manufacturing | 1 | NULL | 0 |
| 1 | 2024-03-29 11:27:28.073025 | 2024-03-29 11:27:43.499535 | 0 | 0 | 行业 | industry | 0 | 行业字典项 | 0 | 6 | 2024-04-01 09:59:44.634897 | 2024-04-01 09:59:44.634897 | 0 | 0 | 交运物流 | logistics | 1 | NULL | 0 |
| 1 | 2024-03-29 11:27:28.073025 | 2024-03-29 11:27:43.499535 | 0 | 0 | 行业 | industry | 0 | 行业字典项 | 0 | 7 | 2024-04-01 10:14:00.135731 | 2024-04-01 10:14:00.135731 | 0 | 0 | 通讯行业 | communication | 1 | NULL | 0 |
| 3 | 2024-03-29 11:33:34.892866 | 2024-03-29 11:33:34.892866 | 0 | 0 | 规模 | scale | 0 | NULL | 0 | 8 | 2024-04-01 10:14:50.871233 | 2024-04-01 10:14:50.871233 | 0 | 0 | 超大型 | Extra large | 3 | NULL | 0 |
| 3 | 2024-03-29 11:33:34.892866 | 2024-03-29 11:33:34.892866 | 0 | 0 | 规模 | scale | 0 | NULL | 0 | 9 | 2024-04-01 10:15:00.389472 | 2024-04-01 10:15:00.389472 | 0 | 0 | 大型 | large | 3 | NULL | 0 |
| 3 | 2024-03-29 11:33:34.892866 | 2024-03-29 11:33:34.892866 | 0 | 0 | 规模 | scale | 0 | NULL | 0 | 10 | 2024-04-01 10:15:16.022855 | 2024-04-01 10:15:16.022855 | 0 | 0 | 中型 | medium size | 3 | NULL | 0 |
| 3 | 2024-03-29 11:33:34.892866 | 2024-03-29 11:33:34.892866 | 0 | 0 | 规模 | scale | 0 | NULL | 0 | 11 | 2024-04-01 10:15:29.581758 | 2024-04-01 10:15:29.581758 | 0 | 0 | 小型 | small | 3 | NULL | 0 |
| 3 | 2024-03-29 11:33:34.892866 | 2024-03-29 11:33:34.892866 | 0 | 0 | 规模 | scale | 0 | NULL | 0 | 12 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 微型 | miniature | 3 | NULL | 0 |
| 2 | 2024-03-29 11:32:53.154392 | 2024-03-29 11:32:53.154392 | 0 | 0 | 职位 | position | 0 | NULL | 0 | 13 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 会长 | President | 2 | NULL | 0 |
| 2 | 2024-03-29 11:32:53.154392 | 2024-03-29 11:32:53.154392 | 0 | 0 | 职位 | position | 0 | NULL | 0 | 14 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 名誉会长 | Honorary President | 2 | NULL | 0 |
| 2 | 2024-03-29 11:32:53.154392 | 2024-03-29 11:32:53.154392 | 0 | 0 | 职位 | position | 0 | NULL | 0 | 15 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 副会长 | Vice president | 2 | NULL | 0 |
| 2 | 2024-03-29 11:32:53.154392 | 2024-03-29 11:32:53.154392 | 0 | 0 | 职位 | position | 0 | NULL | 0 | 16 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 理事 | director | 2 | NULL | 0 |
| 2 | 2024-03-29 11:32:53.154392 | 2024-03-29 11:32:53.154392 | 0 | 0 | 职位 | position | 0 | NULL | 0 | 17 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 会员 | member | 2 | NULL | 0 |
| 4 | 2024-03-29 11:34:57.600602 | 2024-03-29 11:34:57.600602 | 0 | 0 | 专委会 | special | 0 | NULL | 0 | 18 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 智能制造 | intelligent | 4 | NULL | 0 |
| 4 | 2024-03-29 11:34:57.600602 | 2024-03-29 11:34:57.600602 | 0 | 0 | 专委会 | special | 0 | NULL | 0 | 19 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 生物医药 | biology | 4 | NULL | 0 |
| 4 | 2024-03-29 11:34:57.600602 | 2024-03-29 11:34:57.600602 | 0 | 0 | 专委会 | special | 0 | NULL | 0 | 20 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 金融投资 | finance | 4 | NULL | 0 |
| 4 | 2024-03-29 11:34:57.600602 | 2024-03-29 11:34:57.600602 | 0 | 0 | 专委会 | special | 0 | NULL | 0 | 21 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:15:42.507014 | 0 | 0 | 轨道交通 | track | 4 | NULL | 0 |
| 4 | 2024-03-29 11:34:57.600602 | 2024-03-29 11:34:57.600602 | 0 | 0 | 专委会 | special | 0 | NULL | 0 | 22 | 2024-04-01 10:15:42.507014 | 2024-04-01 10:28:06.907779 | 0 | 0 | 建设行业 | construction | 4 | NULL | 0 |
| 23 | 2024-04-15 19:37:03.758482 | 2024-05-13 20:50:44.665558 | 1 | 0 | 圈子分类 | FriendsClassify | 0 | NULL | 0 | 24 | 2024-04-15 19:37:44.916770 | 2024-05-13 20:50:49.870535 | 1 | 0 | 业务资源对接 | BusinessResourceDocking | 23 | NULL | 0 |
| 23 | 2024-04-15 19:37:03.758482 | 2024-05-13 20:50:44.665558 | 1 | 0 | 圈子分类 | FriendsClassify | 0 | NULL | 0 | 25 | 2024-04-15 19:38:00.025783 | 2024-05-13 20:50:52.175268 | 1 | 0 | 人才招聘 | Recruitment | 23 | NULL | 0 |
+----+----------------------------+----------------------------+------+--------+--------------+-----------------+----------+-----------------+------+----+----------------------------+----------------------------+------+--------+--------------------+-------------------------+----------+-------------+------+
20 rows in set (0.00 sec)增加一条查询条件：
sql
select a.title, a.id as parent_id, b.title AS son_title , b.id as son_id
FROM qinghe_dictionary a
JOIN qinghe_dictionary b
ON a.id = b.parentId
WHERE a.id = 1bash
+--------+-----------+--------------+--------+
| title | parent_id | son_title | son_id |
+--------+-----------+--------------+--------+
| 行业 | 1 | 制造业 | 5 |
| 行业 | 1 | 交运物流 | 6 |
| 行业 | 1 | 通讯行业 | 7 |
+--------+-----------+--------------+--------+
3 rows in set (0.01 sec)NATURAL JOIN
NATURAL JOIN 是一种特殊的 JOIN，它根据两个表具有相同名称的列来匹配行。它与等值 JOIN 类似，但不需要指定列名。但是，它只适用于两个表具有相同名称的列的情况。 自动匹配同名列：
- 不指定连接条件： 由于 NATURAL JOIN 自动处理连接条件，因此查询语句中不需要 ON 子句。这简化了编写查询的过程，但也可能导致意外的结果，特别是当表之间存在多个同名列时。
- 结果集中列的处理： 在结果集中，NATURAL JOIN 只保留一份来自连接条件的列（即同名列）。这意味着如果两个表都有一个名为 id 的列，那么在结果集中只会有一个 id 列，而不是两个。
- 潜在的问题： 如果两个表之间的同名列不是预期的连接键，或者有多个可能的连接键，NATURAL JOIN 可能会导致非预期的结果。此外，如果两个表之间没有任何同名列，NATURAL JOIN 将产生空的结果集，因为它找不到任何可以连接的条件。
- 性能考虑： NATURAL JOIN 的性能取决于数据库系统的实现。在某些情况下，它可能会比明确指定连接条件的 JOIN 慢，因为在执行 NATURAL JOIN 时，数据库需要额外的工作来识别同名列。
假设我们有两个表 employees 和 departments，它们都包含一个名为 department_id 的列，代表部门ID。 -- 表 employees
bash
+----+----------------+---------------+
| id | name | department_id |
+----+----------------+---------------+
| 1 | John Doe | 10 |
| 2 | Jane Smith | 20 |
| 3 | Alice Johnson | 10 |
+----+----------------+---------------+-- 表 departments
bash
+----+----------------+
| id | department_name|
+----+----------------+
| 10 | Sales |
| 20 | Marketing |
+----+----------------+sql
SELECT * FROM employees NATURAL JOIN departments;bash
+----+----------------+---------------+----------------+
| id | name | department_id | department_name|
+----+----------------+---------------+----------------+
| 1 | John Doe | 10 | Sales |
| 2 | Jane Smith | 20 | Marketing |
| 3 | Alice Johnson | 10 | Sales |
+----+----------------+---------------+----------------+