连接查询：INNER JOIN与LEFT JOIN

学习前提

理解主键、外键概念和聚合；能区分课程表与记录表。

本课完成后你能做什么

关联课程与学习记录，保留尚未开始的课程。

先理解机制

主表courses一行代表一门课，sessions一行代表一次学习。一对多连接会让同一门课出现多行，这不是数据库重复数据故障，而是连接粒度。

INNER JOIN仅保留匹配行；LEFT JOIN保留左侧每一行，右侧无匹配时填NULL。因此统计学习次数要用COUNT(s.id)，不能用COUNT(*)把占位行算成一次学习。

针对右表的过滤放在WHERE可能排除未匹配行。若需求是保留所有课程但仅关联合格记录，通常将相应条件写在ON中，再检查聚合结果。

可运行示例

本系列使用SQLite 3方言。打开一个全新的内存练习数据库，例如终端执行 sqlite3 :memory:，再执行本课整段SQL。每章都自带建表和样例数据，不连接网站业务数据库。

CREATE TABLE courses(id INTEGER PRIMARY KEY,name TEXT);
CREATE TABLE sessions(id INTEGER PRIMARY KEY,course_id INTEGER,minutes INTEGER);
INSERT INTO courses VALUES(1,'Python'),(2,'SQL'),(3,'Java');
INSERT INTO sessions VALUES(1,1,25),(2,1,35),(3,2,20);
SELECT c.name,COUNT(s.id) AS sessions,COALESCE(SUM(s.minutes),0) AS total
FROM courses c LEFT JOIN sessions s ON s.course_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;

推演与验证

Python为2次60分钟，SQL为1次20分钟，Java为0次0分钟。这里0表示没有学习记录，是明确的业务定义。

常见错误与边界

同时连接两张一对多明细表可能造成乘法式扩张并重复累计。应先分别聚合到课程粒度，再连接汇总。

动手练习

仅统计不少于30分钟的学习，但仍展示所有课程。

参考解法与理由

在ON中加入AND s.minutes>=30。Python为1次35分钟，SQL与Java均为0次0分钟；放WHERE会丢失后两门课。

深入实验：LEFT JOIN为何把零记录课程丢掉

报表要列出所有课程，包括没有完成记录的课程。先做LEFT JOIN，再在WHERE里限制右表为done，会把补出来的NULL行过滤掉。下面对比条件放在ON与WHERE的位置，并解释COUNT的选择。

CREATE TABLE course(id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE attempt(id INTEGER PRIMARY KEY, course_id INTEGER, status TEXT);
INSERT INTO course VALUES(1,'HTML'),(2,'CSS'),(3,'SQL');
INSERT INTO attempt VALUES(10,1,'done'),(11,1,'todo'),(12,2,'todo');
SELECT c.title, COUNT(a.id) AS completed
FROM course c LEFT JOIN attempt a
ON a.course_id = c.id AND a.status = 'done'
GROUP BY c.id, c.title ORDER BY c.id;
SELECT c.title, COUNT(a.id) AS completed
FROM course c LEFT JOIN attempt a ON a.course_id = c.id
WHERE a.status = 'done'
GROUP BY c.id, c.title ORDER BY c.id;

预期观察

第一个报表返回HTML|1、CSS|0、SQL|0；第二个只剩HTML|1。

原因、取舍与适用边界

ON决定右表哪些行能匹配，匹配不到仍保留左表课程；WHERE在连接结果上筛选，NULL = done不是TRUE。COUNT(a.id)忽略补齐的NULL，而COUNT(*)会把那一行也计成1。若再连接另一张一对多表，尝试次数还可能成倍增加；应先分别按course_id聚合，再连接聚合结果，不要用随手DISTINCT掩盖错误粒度。

进阶练习

要显示“总尝试数”和“完成数”两列，是否还能在ON里只保留done？

解法与推理

不能，否则总数也只剩完成记录。连接全部尝试，COUNT(a.id)统计总数，SUM(CASE WHEN a.status = 'done' THEN 1 ELSE 0 END)统计完成数；再用完全无尝试的课程检查两列都为0。

进一步阅读

SQLite SQL reference