|
一、Join关联类型概述
在SQL查询中，Join用于将两个或多个表中的记录根据关联字段组合起来，是关系型数据库中数据查询的核心操作之一。不同的Join类型决定了如何处理两个表中不匹配的记录，其在数据整合、多表查询场景中发挥着关键作用，是数据分析、业务系统开发中必须掌握的基础技术点。
二、内连接（INNER JOIN）
1. 技术原理
内连接是最常用的Join类型之一，它只返回两个表中关联字段匹配的记录。即只有当两个表中的行在连接条件上满足匹配关系时，才会被包含在结果集中，不匹配的行将被过滤掉。
2. 案例与代码实现
案例场景：现有学生表（students）和成绩表（scores），需查询有成绩记录的学生信息及对应的成绩。
- 学生表（students）结构：id（学生ID）、name（学生姓名）
- 成绩表（scores）结构：id（成绩ID）、student_id（关联学生ID）、score（成绩）
代码实现：
-- 创建学生表并插入数据
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
INSERT INTO students (id, name) VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');
-- 创建成绩表并插入数据
CREATE TABLE scores (
id INT PRIMARY KEY,
student_id INT,
score INT,
FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO scores (id, student_id, score) VALUES
(1, 1, 90),
(2, 2, 85);
-- 内连接查询
SELECT s.id, s.name, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;查询结果：
三、左连接（LEFT JOIN/LEFT OUTER JOIN）
1. 技术原理
左连接以左表为基准，返回左表中的所有记录，以及右表中与左表关联字段匹配的记录。如果右表中没有匹配的记录，则结果集中右表的对应字段将显示为NULL。
2. 案例与代码实现
案例场景：使用上述学生表和成绩表，查询所有学生的信息及对应的成绩（包括没有成绩的学生）。
代码实现：
-- 左连接查询
SELECT s.id, s.name, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id; 查询结果：
| id | name | score |
|---|
| 1 | 张三 | 90 | | 2 | 李四 | 85 | | 3 | 王五 | NULL |
四、右连接（RIGHT JOIN/RIGHT OUTER JOIN）
1. 技术原理
右连接与左连接逻辑相反，以右表为基准，返回右表中的所有记录，以及左表中与右表关联字段匹配的记录。若左表中无匹配记录，左表对应字段显示为NULL。
2. 案例与代码实现
案例场景：使用上述学生表和成绩表，查询所有成绩记录及对应的学生信息（若成绩表中有无效学生ID，也会显示）。
代码实现：
-- 向成绩表插入一条无效学生ID的记录
INSERT INTO scores (id, student_id, score) VALUES
(3, 4, 70);
-- 右连接查询
SELECT s.id, s.name, sc.score
FROM students s
RIGHT JOIN scores sc ON s.id = sc.student_id; 查询结果：
| id | name | score |
|---|
| 1 | 张三 | 90 | | 2 | 李四 | 85 | | NULL | NULL | 70 |
五、全连接（FULL JOIN/FULL OUTER JOIN）
1. 技术原理
全连接返回左表和右表中的所有记录，当两个表中的记录匹配时，显示组合后的结果；当不匹配时，未匹配的一侧字段显示为NULL。需要注意的是，某些数据库（如MySQL）不直接支持FULL JOIN，可通过左连接和右连接结合UNION实现。
2. 案例与代码实现
案例场景：查询所有学生和所有成绩记录的关联信息，包括没有成绩的学生和没有对应有效学生的成绩。
代码实现（以支持FULL JOIN的数据库为例）：
SELECT s.id, s.name, sc.score
FROM students s
FULL JOIN scores sc ON s.id = sc.student_id; MySQL中实现全连接：
SELECT s.id, s.name, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
UNION
SELECT s.id, s.name, sc.score
FROM students s
RIGHT JOIN scores sc ON s.id = sc.student_id; 查询结果：
| id | name | score |
|---|
| 1 | 张三 | 90 | | 2 | 李四 | 85 | | 3 | 王五 | NULL | | NULL | NULL | 70 |
六、交叉连接（CROSS JOIN）
1. 技术原理
交叉连接会返回两个表中所有可能的记录组合，即左表的每一行与右表的每一行都将形成一条新记录，结果集的行数为两个表行数的乘积，通常需要配合WHERE子句筛选有意义的数据。
2. 案例与代码实现
案例场景：查询学生表和课程表（courses）的所有可能组合，用于生成学生选课初始列表。
- 课程表（courses）结构：id（课程ID）、name（课程名称）
代码实现：
-- 创建课程表并插入数据
CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
INSERT INTO courses (id, name) VALUES
(1, '数学'),
(2, '语文');
-- 交叉连接查询
SELECT s.name AS student_name, c.name AS course_name
FROM students s
CROSS JOIN courses c;查询结果：
| student_name | course_name |
|---|
| 张三 | 数学 | | 张三 | 语文 | | 李四 | 数学 | | 李四 | 语文 | | 王五 | 数学 | | 王五 | 语文 |
七、自连接（SELF JOIN）
1. 技术原理
自连接是指表与自身进行连接，将一张表当作两张不同的表来处理，通常用于查询表中具有层级关系或关联关系的记录，如员工表中查询员工及其所属领导信息。
2. 案例与代码实现
案例场景：在员工表（employees）中，查询每个员工及其直接领导的姓名。
- 员工表（employees）结构：id（员工ID）、name（员工姓名）、manager_id（领导ID，关联自身id）
代码实现：
-- 创建员工表并插入数据
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, '老板', NULL),
(2, '员工A', 1),
(3, '员工B', 1),
(4, '员工C', 2);
-- 自连接查询
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;查询结果：
| employee_name | manager_name |
|---|
| 老板 | NULL | | 员工A | 老板 | | 员工B | 老板 | | 员工C | 员工A |
|
发表于 2025-9-11 10:26:42