MySQL连接类型详解

一、什么是SQL连接

SQL连接(JOIN)用于将两个或多个表中的数据组合起来，基于它们之间的相关列。通过连接，可以从多个表中获取需要的数据，而不必将所有数据都存储在一个表中。

为什么需要连接？

• 遵循数据库设计的规范化原则，减少数据冗余

• 可以灵活地组合不同表的数据，获取更全面的信息

• 提高查询效率和数据管理的灵活性

二、MySQL连接类型及示例

2.1 内连接(INNER JOIN)

定义：内连接是最常用的连接类型，只返回两个表中匹配条件的行。

语法：

SELECT 列名
FROM 表1
INNER JOIN 表2
ON 表1.关联列 = 表2.关联列;

示例：

假设有以下两个表：

学生表(students)

id	name	age
1	张三	20
2	李四	21
3	王五	19
4	赵六	22

成绩表(scores)

id	student_id	course	score
1	1	数学	90
2	1	英语	85
3	2	数学	95
4	3	英语	80

查询学生及其成绩(内连接)：

SELECT 
    students.id, 
    students.name, 
    scores.course, 
    scores.score
FROM students
INNER JOIN scores
ON students.id = scores.student_id;

结果：

id	name	course	score
1	张三	数学	90
1	张三	英语	85
2	李四	数学	95
3	王五	英语	80

说明：赵六没有成绩记录，所以没有出现在结果中。

2.2 左外连接(LEFT JOIN/LEFT OUTER JOIN)

定义：左连接返回左表的所有行，以及右表中匹配条件的行。如果右表中没有匹配，结果中右表的列将显示为NULL。

语法：

SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.关联列 = 表2.关联列;

示例：查询所有学生及其成绩，包括没有成绩的学生

SELECT 
    students.id, 
    students.name, 
    scores.course, 
    scores.score
FROM students
LEFT JOIN scores
ON students.id = scores.student_id;

结果：

id	name	course	score
1	张三	数学	90
1	张三	英语	85
2	李四	数学	95
3	王五	英语	80
4	赵六	NULL	NULL

说明：赵六没有成绩记录，但仍然出现在结果中，成绩相关列显示为NULL。

2.3 右外连接(RIGHT JOIN/RIGHT OUTER JOIN)

定义：右连接返回右表的所有行，以及左表中匹配条件的行。如果左表中没有匹配，结果中左表的列将显示为NULL。

语法：

SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.关联列 = 表2.关联列;

示例：查询所有成绩记录及其对应的学生信息

SELECT 
    students.id, 
    students.name, 
    scores.course, 
    scores.score
FROM students
RIGHT JOIN scores
ON students.id = scores.student_id;

结果：

id	name	course	score
1	张三	数学	90
1	张三	英语	85
2	李四	数学	95
3	王五	英语	80

特殊情况：如果成绩表中有一个不属于任何学生的成绩记录：

id	student_id	course	score
5	5	物理	75

再次执行上述查询，结果将包含这条记录：

id	name	course	score
1	张三	数学	90
1	张三	英语	85
2	李四	数学	95
3	王五	英语	80
NULL	NULL	物理	75

2.4 全外连接(FULL JOIN/FULL OUTER JOIN)

定义：全连接返回两个表的所有行。当某一行在另一个表中没有匹配时，对应的列显示为NULL。

注意：MySQL不直接支持FULL JOIN，但可以通过LEFT JOIN和RIGHT JOIN结合UNION来模拟。

语法：

-- MySQL中模拟全外连接
SELECT 列名 FROM 表1
LEFT JOIN 表2 ON 表1.关联列 = 表2.关联列
UNION
SELECT 列名 FROM 表1
RIGHT JOIN 表2 ON 表1.关联列 = 表2.关联列;

示例：查询所有学生和所有成绩记录

SELECT 
    students.id, 
    students.name, 
    scores.course, 
    scores.score
FROM students
LEFT JOIN scores ON students.id = scores.student_id
UNION
SELECT 
    students.id, 
    students.name, 
    scores.course, 
    scores.score
FROM students
RIGHT JOIN scores ON students.id = scores.student_id;

结果：

id	name	course	score
1	张三	数学	90
1	张三	英语	85
2	李四	数学	95
3	王五	英语	80
4	赵六	NULL	NULL
NULL	NULL	物理	75

2.5 交叉连接(CROSS JOIN)

定义：交叉连接返回两个表的笛卡尔积，即左表的每一行与右表的每一行组合。如果左表有m行，右表有n行，则结果有m*n行。

语法：

-- 显式交叉连接
SELECT 列名 FROM 表1 CROSS JOIN 表2;
​
-- 隐式交叉连接(不推荐，可读性差)
SELECT 列名 FROM 表1, 表2;

示例：查询所有学生和所有课程的组合

-- 首先创建一个课程表
CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
​
INSERT INTO courses VALUES (1, '数学'), (2, '英语'), (3, '物理');
​
-- 交叉连接学生表和课程表
SELECT 
    students.name, 
    courses.name AS course
FROM students
CROSS JOIN courses;

结果：(4个学生 × 3门课程 = 12行)

name	course
张三	数学
张三	英语
张三	物理
李四	数学
李四	英语
李四	物理
王五	数学
王五	英语
王五	物理
赵六	数学
赵六	英语
赵六	物理

2.6 自连接(SELF JOIN)

定义：自连接是一种特殊的内连接，表与自身进行连接。通常用于查询表中具有层次关系的数据，如员工和经理。

语法：

SELECT 列名
FROM 表 AS 别名1
JOIN 表 AS 别名2
ON 别名1.列 = 别名2.列;

示例：查询员工及其经理信息

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    department VARCHAR(50)
);
​
INSERT INTO employees VALUES 
(1, '张经理', NULL, '技术部'),
(2, '李工', 1, '技术部'),
(3, '王工', 1, '技术部'),
(4, '赵经理', NULL, '市场部'),
(5, '钱工', 4, '市场部');
​
-- 自连接查询员工及其经理
SELECT 
    e.name AS employee_name,
    m.name AS manager_name,
    e.department
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

结果：

employee_name	manager_name	department
张经理	NULL	技术部
李工	张经理	技术部
王工	张经理	技术部
赵经理	NULL	市场部
钱工	赵经理	市场部

三、各种连接类型的区别对比

3.1 连接结果对比

连接类型	返回结果特点	支持情况
INNER JOIN	只返回两表中匹配的行	所有SQL数据库支持
LEFT JOIN	返回左表所有行和右表匹配行，右表不匹配则为NULL	所有SQL数据库支持
RIGHT JOIN	返回右表所有行和左表匹配行，左表不匹配则为NULL	所有SQL数据库支持
FULL JOIN	返回两表所有行，不匹配则为NULL	MySQL不直接支持
CROSS JOIN	返回两表的笛卡尔积	所有SQL数据库支持
SELF JOIN	表与自身连接，用于查询层次关系	所有SQL数据库支持

3.2 连接使用场景

连接类型	适用场景
INNER JOIN	需获取两表中都有对应数据的记录
LEFT JOIN	需获取左表全部记录，即使右表没有对应数据
RIGHT JOIN	需获取右表全部记录，即使左表没有对应数据
FULL JOIN	需获取两表所有记录，无论是否有匹配
CROSS JOIN	需生成所有可能的组合，如生成时间表、排班表等
SELF JOIN	处理表内具有层次或关联关系的数据

四、连接查询的最佳实践

4.1 使用显式连接语法

推荐使用显式JOIN语法，而不是隐式连接（逗号分隔表）：

-- 推荐
SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.id;
​
-- 不推荐
SELECT * FROM table1, table2 WHERE table1.id = table2.id;

显式连接语法更清晰，将连接条件与过滤条件分离，减少错误。

4.2 合理使用别名

当连接多个表或自连接时，使用简洁明了的别名：

SELECT 
    s.name AS student_name,
    c.name AS course_name,
    sc.score
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id;

4.3 注意NULL值处理

外连接可能返回NULL值，查询时需注意使用IS NULL或IS NOT NULL判断：

-- 查询没有成绩的学生
SELECT 
    students.id, 
    students.name
FROM students
LEFT JOIN scores ON students.id = scores.student_id
WHERE scores.id IS NULL;

4.4 避免笛卡尔积

除非明确需要，否则不要在没有ON子句的情况下使用JOIN，这会产生笛卡尔积：

-- 危险！会返回m*n行记录
SELECT * FROM table1 JOIN table2;

4.5 MySQL中模拟全外连接

由于MySQL不直接支持FULL JOIN，可以使用以下方式模拟：

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

五、总结

SQL连接是数据库查询中非常重要的操作，掌握不同类型的连接及其使用场景，能够帮助我们高效地从多个表中获取所需数据。本文介绍了MySQL中常用的连接类型：

• 内连接(INNER JOIN)：获取两表匹配的行

• 左连接(LEFT JOIN)：获取左表所有行和匹配的右表行

• 右连接(RIGHT JOIN)：获取右表所有行和匹配的左表行

• 全连接(FULL JOIN)：获取两表所有行，MySQL需用UNION模拟

• 交叉连接(CROSS JOIN)：获取两表的笛卡尔积

• 自连接(SELF JOIN)：表与自身连接，处理层次数据

在实际应用中，应根据具体需求选择合适的连接类型，并遵循最佳实践，编写清晰、高效的连接查询。