SQL 笔记——连接（JOIN）

什么是 JOIN？

SQL 中的 JOIN 是用于跨表查询的语法。它能根据一张表的字段与另一张表的字段之间的关系，把行组合起来返回。

表结构

我们先创建两个表来演示各种 JOIN：

-- 学生表
CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- 成绩表
CREATE TABLE Scores (
  score_id INT PRIMARY KEY,
  student_id INT,
  subject VARCHAR(50),
  score INT
);

插入测试数据：

INSERT INTO Students VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO Scores VALUES
(101, 1, 'Math', 90),
(102, 1, 'English', 85),
(103, 2, 'Math', 75),
(104, 4, 'Math', 88);  -- 注意 student_id=4，在学生表中不存在

INNER JOIN（内连接） (等价于JOIN)

作用：返回两个表中匹配的记录（关联字段相等时才保留）。

SELECT s.name, sc.subject, sc.score
FROM Students s
INNER JOIN Scores sc ON s.student_id = sc.student_id;

结果：

只保留在两张表中都存在的 student_id。

LEFT JOIN（左连接）

作用：返回左表的所有记录，如果右表没有匹配，则补 NULL。

SELECT s.name, sc.subject, sc.score
FROM Students s
LEFT JOIN Scores sc ON s.student_id = sc.student_id;

结果：

所有 Students 中的学生都显示出来，即使 Charlie 没有成绩。

RIGHT JOIN（右连接）

作用：返回右表的所有记录，如果左表没有匹配，则补 NULL。

SELECT s.name, sc.subject, sc.score
FROM Students s
RIGHT JOIN Scores sc ON s.student_id = sc.student_id;

结果：

成绩表中 student_id=4 的数据保留，即使学生表没有该学生。

FULL OUTER JOIN（全连接）

MySQL 不直接支持 FULL OUTER JOIN，可使用 UNION 模拟：

-- 模拟 FULL OUTER JOIN
SELECT s.name, sc.subject, sc.score
FROM Students s
LEFT JOIN Scores sc ON s.student_id = sc.student_id

UNION

SELECT s.name, sc.subject, sc.score
FROM Students s
RIGHT JOIN Scores sc ON s.student_id = sc.student_id;

结果：

所有匹配与不匹配的行都保留。

CROSS JOIN（笛卡尔积）

作用：每条左表记录与右表每条记录组合，不需要任何匹配条件。

SELECT s.name, sc.subject
FROM Students s
CROSS JOIN Scores sc;

有 3 个学生，4 条成绩记录，结果将是：

3 × 4 = 12 条记录（慎用，数据量大时性能差）

连接类型对比表

常见用法

① 查询没有成绩的学生（LEFT JOIN + WHERE）

SELECT s.name
FROM Students s
LEFT JOIN Scores sc ON s.student_id = sc.student_id
WHERE sc.score IS NULL;

用 LEFT JOIN 搭配 IS NULL 可筛出“没有”的记录。

② 多表连接

SELECT s.name, sc.subject, sc.score, t.teacher_name
FROM Students s
JOIN Scores sc ON s.student_id = sc.student_id
JOIN Teachers t ON sc.subject = t.subject;

可以多表连查，连接顺序要清晰。

③ 给 INNER JOIN 加筛选条件

SELECT s.name, sc.subject
FROM Students s
JOIN Scores sc 
  ON s.student_id = sc.student_id
WHERE sc.score >= 85;

WHERE 筛选是在 JOIN 之后，ON 条件是 JOIN 时的判断依据。

总结

• 开发中最常用的是 INNER JOIN 和 LEFT JOIN。
• LEFT JOIN + IS NULL 是判断“未匹配”的常见手法。
• JOIN 时建议都加表别名，简洁清晰。
• 多表连接时，保持主外键清晰，注意关联字段。