本文旨在阐明在 sql 查询中处理多条件连接与结果过滤的正确方法。我们将深入探讨 `on` 子句和 `where` 子句在 `inner join` 中的不同作用，并纠正将非连接条件错误放置于 `on` 子句中的常见误区，提供示例代码和最佳实践，确保查询的准确性和性能。
在构建复杂的 SQL 查询时，尤其当涉及多个表的连接并需要对结果进行精细过滤时，正确理解和使用 ON 子句与 WHERE 子句至关重要。一个常见的误区是将所有过滤条件，无论其是否直接参与表的连接逻辑，都一股脑地放入 ON 子句中。这不仅可能导致语法错误，还可能在某些连接类型（如 LEFT JOIN）中产生意想不到的结果。
理解 ON 子句与 WHERE 子句的区别
1. ON 子句：定义连接条件
ON 子句的主要作用是指定两个表之间如何进行关联。它定义了连接的逻辑，即哪些行应该被匹配。对于 INNER JOIN 而言，ON 子句中的条件必须为真，两个表中的行才能被组合成结果集中的一行。如果 ON 子句中包含非连接条件，这些条件会在连接操作发生时进行评估，但其主要目的是为了建立表之间的关系。
2. WHERE 子句：过滤最终结果集
WHERE 子句用于在连接操作完成并生成中间结果集之后，对这个结果集进行最终的行过滤。无论使用哪种类型的连接（INNER JOIN, LEFT JOIN, RIGHT JOIN 等），WHERE 子句都会在所有连接操作执行完毕后，根据指定的条件筛选出最终需要返回的行。
常见错误与解决方案
原始问题中展示的错误在于尝试将 course.deleted_at is null 和 course.publish="true" 这类针对 course 表的过滤条件直接放入 INNER JOIN 的 ON 子句中，与连接条件 course."userId"=users.id 并列。虽然在 INNER JOIN 的场景下，将这些条件放在 ON 子句中从逻辑上可能与放在 WHERE 子句中产生相同的结果（因为 INNER JOIN 只返回匹配的行），但从语义上和最佳实践上讲，它们属于对最终结果的过滤，而非定义连接关系。更重要的是，在某些数据库系统或特定查询优化器下，这种做法可能导致语法错误或性能问题。
例如，原始查询中的错误信息 SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "INNER" 明确指出在 INNER JOIN 后面紧跟 WHERE 子句是语法错误的，这暗示了数据库解析器期望 ON 子句后是下一个 JOIN 子句，而不是一个独立于连接关系的 WHERE 子句。
正确的处理方式是将这些非连接条件移至 WHERE 子句。
示例代码：正确应用多条件过滤
以下是根据上述原则修正后的 SQL 查询：
SELECT
users.id AS user_id,
users.name,
course.id AS course_id,
course."courseDisplayName",
course.created_at,
subjects.id AS subject_id,
subjects.subject
FROM
users
INNER JOIN
course ON course."userId" = users.id
INNER JOIN
subjects ON course."subjectId" = subjects.id
WHERE
course.deleted_at IS NULL
AND course.publish;代码解析：
- INNER JOIN course ON course."userId" = users.id: 这里清晰地定义了 users 表和 course 表之间的连接关系，即通过 users.id 和 course."userId" 进行匹配。
- INNER JOIN subjects ON course."subjectId" = subjects.id: 同样，这定义了 course 表和 subjects 表之间的连接关系。
- WHERE course.deleted_at IS NULL AND course.publish: 在所有的连接操作完成后，WHERE 子句对生成的中间结果集进行过滤。它确保只选择那些 deleted_at 列为 NULL (表示未删除) 并且 publish 列为 true (已发布) 的课程记录。
关于 course.publish="true" 的注意事项：
如果 course.publish 列的数据类型是布尔型（BOOLEAN），那么直接使用 AND course.publish 就足够了，它会自动评估为 TRUE 或 FALSE。如果它是字符串类型（VARCHAR 或 TEXT），并且存储的是 "true" 或 "false" 字符串，那么 AND course.publish = 'true' (或 AND course.publish = "true", 取决于数据库的字符串引号规则) 才是正确的写法。在 PostgreSQL 中，布尔类型通常直接使用 TRUE 或 FALSE，或者其字面量表示。示例代码中使用了 course.publish，这暗示 publish 字段很可能是一个布尔类型。
总结与最佳实践
- ON 子句用于定义表之间的连接关系。 它的条件决定了哪些行可以被连接起来。
- WHERE 子句用于在连接操作完成后，对最终的结果集进行行过滤。 它的条件决定了哪些行应该被包含在查询的最终输出中。
- 保持职责分离：将连接逻辑和结果过滤逻辑分开，可以使 SQL 查询更易读、更易维护，并且在不同类型的连接（尤其是 LEFT JOIN）中避免潜在的逻辑错误。
- 考虑数据类型：在编写过滤条件时，务必注意列的数据类型，特别是布尔型和字符串类型，以确保条件判断的准确性。
遵循这些原则，可以编写出更健壮、更高效的 SQL 查询。
