SQL里的额外条件 JOIN： ON ... AND ...

你已经知道怎么用 JOIN 来连接表了。但实际开发中，光靠主键匹配其实远远不够。经常会遇到这种需求：只连接那些还要满足额外条件的数据——比如只要活跃的记录、只要今年的数据、或者只要已完成的订单。

这时候就要用到 ON 里加 AND 的扩展写法了。

在 JOIN ... ON 里加额外条件，可以精确控制哪些行参与连接，而且是在 SQL 开始构建结果之前就筛选了。这样写的查询：

• 更快（通过 JOIN 的行更少），
• 更精准（过滤发生在连接阶段），
• 用 LEFT JOIN 时更可控（和 WHERE 过滤不一样）。

例子：只要活跃的课程报名

假设 enrollments 表里有学生报名状态：active、dropped、pending。

students 表：

更新下 enrollments 表：

courses 表：

现在我们想查只有那些有活跃课程的学生：

SELECT 
    students.name AS student_name,
    courses.name AS course_name
FROM students 
INNER JOIN enrollments 
    ON students.id = enrollments.student_id
    AND enrollments.status = 'active'
INNER JOIN courses 
    ON enrollments.course_id = courses.id;

结果：

这里我们把 AND enrollments.status = 'active' 写在 ON 里，这样连接的时候只会连活跃的记录，而不是连完再过滤。

为啥不用 WHERE？

你也可以这样写：

...
WHERE enrollments.status = 'active'

但这样在 LEFT JOIN 时行为就不一样了。WHERE 过滤会把没匹配到的行（NULL）都删掉，这样 LEFT JOIN 就变成 INNER JOIN 了。

而 AND enrollments.status = 'active' 写在 ON 里，是直接限制参与连接的行——决定哪些行能进连接，不是连完再过滤。

这种写法特别重要，比如你想保留一张表里的所有行，即使另一张表没有匹配（做报表和分析时很常见）。

更多 ON ... AND ... 的用法例子

students 表：

enrollments 表：

courses 表：

例子：只要今年的课程

SELECT
    students.name,
    courses.name,
    enrollments.enrolled_at
FROM students
JOIN enrollments
    ON students.id = enrollments.student_id
    AND EXTRACT(YEAR FROM enrollments.enrolled_at) = EXTRACT(YEAR FROM CURRENT_DATE)
JOIN courses
    ON enrollments.course_id = courses.id;

这里我们只连那些属于今年的记录。

例子：排除某个值

JOIN enrollments
    ON students.id = enrollments.student_id
    AND enrollments.status != 'dropped'

我们在连接阶段就把退课的学生排除了，而不是连完再过滤。

条件写在ON 里，PostgreSQL 可以优化连接计划，处理的行更少。数据量大时，这种内部过滤比连完再过滤高效多了。

JOIN ON —— 不只是主键

很多人以为 ON 只能写 id = id。其实你可以写：

• 逻辑操作符：AND、OR、NOT
• 比较：>、<、<>、BETWEEN、IN
• 表达式：EXTRACT、DATE_TRUNC、COALESCE、NULLIF

一起组合用

students 表：

faculties 表：

courses 表：

enrollments 表：

SELECT
    s.name AS student_name,
    c.name AS course_name,
    f.name AS faculty_name
FROM students s
JOIN enrollments e
    ON s.id = e.student_id
    AND e.status = 'active'
JOIN courses c
    ON e.course_id = c.id
    AND c.name != 'Физкультура'
JOIN faculties f
    ON c.faculty_id = f.id
    AND f.name IS NOT NULL;

这里我们同时过滤：

• 活跃记录，
• 不是“Физкультура”的课程，
• 有名字的院系。

查询结果：

希望你喜欢这节讲座。你以后写 SQL 查询时会经常用到多个带过滤的 JOIN。几乎每次都用得上，真的 :)