SQL 查询时，ON和WHERE都能筛选数据，但用错结果可能差很远。ON 用在 JOIN 时指定连接条件，筛选关联表数据；WHERE 在 JOIN 后对结果集筛选，直接排除不符合条件的行。只有理清了，我们才能写出对的查询。具体用法如下：

一、ON的基础语法

SQL 多表查询时， ON 子句用在连接（JOIN）多个表时指定连接条件，决定两个表中的哪些行应该被匹配在一起，定义两个表之间如何关联，通常与 JOIN 关键字配合使用。

1、语法结构

-- 基本结构：在 JOIN 后使用 ON 指定连接条件
SELECT 列名1, 列名2, ...
FROM 表1
[INNER | LEFT | RIGHT | FULL] JOIN 表2
  ON 表1.关联列 = 表2.关联列  -- 核心：ON 后的连接条件
[WHERE 其他过滤条件];  -- 可选：对连接结果进一步过滤

示例：
假设有 students 表（学生信息）和 scores 表（成绩信息），通过 student_id 关联：

-- 查询学生姓名及其对应的成绩
SELECT students.name, scores.score
FROM students
INNER JOIN scores
  ON students.student_id = scores.student_id;  -- 连接条件：学生ID匹配

2、语法元素

(1)JOIN 类型

• INNER JOIN（内连接）：只保留两表中满足 ON 条件的行。
• LEFT JOIN（左连接）：保留左表所有行，右表中不满足条件的行用 NULL 填充。
• RIGHT JOIN（右连接）：保留右表所有行，左表中不满足条件的行用 NULL 填充。
• FULL JOIN（全连接）：保留两表所有行，不匹配的部分用 NULL 填充（部分数据库如 MySQL 不支持，MySQL 可通过 LEFT JOIN + UNION + RIGHT JOIN 模拟全连接）。

(2)ON 子句的核心作用
指定两表的关联规则，通常通过相同含义的列（如主键和外键）建立连接，例如 表1.id = 表2.关联id。

• 注意：ON 是连接时的过滤条件，仅用于决定哪些行需要被连接。
• 区别于 WHERE：WHERE 是对连接后的结果进行过滤。

(3)连接条件的扩展
ON 后可以使用更复杂的条件（不仅仅是等于），例如：

-- 关联学生表和成绩表，且成绩大于80分
SELECT students.name, scores.score
FROM students
LEFT JOIN scores
  ON students.student_id = scores.student_id 
  AND scores.score > 80;  -- 连接时就过滤成绩

3、注意事项

• ON 必须与 JOIN 配合使用，不能单独使用。

  特殊情况：CROSS JOIN（交叉连接）本质是无条件连接，无需 ON 子句。

• 当连接多个表时，每个 JOIN 都需要单独的 ON 子句指定与前一个表的关联条件。

• 为避免列名冲突，建议使用表名.列名的形式（如 students.student_id），也可给表起别名简化：

  -- 表别名简化写法
  SELECT s.name, sc.score
  FROM students s  -- 别名 s
  JOIN scores sc   -- 别名 sc
    ON s.student_id = sc.student_id;
  

通过 ON 子句，SQL 能精确控制多表之间的关联逻辑，是实现复杂查询的基础。

二、WHERE的基础语法

SQL 的 WHERE 子句用在查询中筛选符合特定条件的记录，通常与 SELECT、UPDATE、DELETE 等语句配合使用，以精确控制操作的数据范围。

1、语法结构

-- 语法结构：在查询中筛选符合条件的记录
SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件表达式;  -- 关键子句：指定筛选条件


-- 示例：从学生表中查询年龄大于18岁的学生姓名和年龄
SELECT name, age
FROM students
WHERE age > 18;  -- 筛选条件：年龄大于18

2、语法元素

(1)WHERE 子句的位置
必须位于 FROM 子句之后，可选项（如 GROUP BY、ORDER BY）之前，如果存在 JOIN，ON 子句在 WHERE 子句之前执行。

(2)条件表达式的组成
条件表达式由 列名、运算符、值 组成，用于定义筛选规则：

• 列名：表中用于筛选的字段（如 age、score）。
• 运算符：连接列名和值的比较符号，常见类型：
• 比较运算符：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、!= 或 <>（不等于）。
• 逻辑运算符：AND（并且）、OR（或者）、NOT（否定）。
• 特殊运算符：BETWEEN...AND...（在范围内）、IN（在列表中）、LIKE（模糊匹配）、IS NULL（为空）。
• 值：与列进行比较的具体数据（如 18、'男'、'2023-01-01'）。

(3)常用条件示例

• 等于某个值：

  WHERE gender = '男'  -- 筛选性别为男的记录
  

• 范围筛选：

  WHERE score BETWEEN 60 AND 100  -- 筛选分数在60到100之间的记录
  

• 多条件组合：

  WHERE age > 18 AND gender = '女'  -- 筛选年龄>18且性别为女的记录
  

• 模糊匹配（% 表示任意字符，_ 表示单个字符）：

  WHERE name LIKE '张%'  -- 筛选姓张的记录（如张三、张伟）
  

• 判断空值：

  WHERE email IS NULL  -- 筛选邮箱为空的记录
  

3、注意事项

• 字符串值必须用单引号 ' ' 包裹（如 '男'），数值和日期无需引号。
• WHERE 子句不会影响表的原始数据，仅决定查询或操作的结果范围。
• 复杂条件可通过括号 () 调整优先级，例如：

  WHERE (age > 18 OR score > 90) AND gender = '男'
  

三、ON与WHERE的对比与应用

1、语法结构差异

(1)基本语法对比

• ON 的语法结构

  -- ON 紧跟在 JOIN 之后，指定表A和表B的连接条件
  SELECT * 
  FROM 表A 
  JOIN 表B 
    ON 表A.id = 表B.a_id;  -- 这里的 ON 用于定义两表连接关系
  

• 仅能与 JOIN 关键字配合使用，用于指定表之间的连接条件
• 语法格式：表1 JOIN 表2 ON 连接条件

• WHERE 的语法结构

  -- WHERE 单独使用，过滤表A中id大于1的记录
  SELECT * 
  FROM 表A 
  WHERE id > 1;  -- 这里的 WHERE 用于过滤单表数据
  

  -- WHERE 在 JOIN 之后使用，先连接表再过滤结果
  SELECT * 
  FROM 表A 
  JOIN 表B 
    ON 表A.id = 表B.a_id  -- ON 定义连接条件
  WHERE 表B.score > 60;   -- WHERE 过滤连接后的结果
  

• 可单独使用，也可与 JOIN 配合使用，用于过滤数据
• 语法格式：SELECT ... FROM ... WHERE 过滤条件

(2)语法位置差异

• ON 的专属位置

  SELECT ...
  FROM TableA 
  [INNER|LEFT|RIGHT] JOIN TableB  -- 指定连接类型
      ON <join_condition>  -- 必须紧跟在 JOIN 子句后，不可省略
  

  注释：ON 是 JOIN 子句的必要组成部分，定义两个表如何建立关联，没有 ON 的 JOIN 会导致语法错误。

• WHERE 的通用位置

  SELECT ...
  FROM ...  -- 可以是单表或多表连接
  [WHERE <filter_condition>]  -- 可选子句，可在各种查询中使用
  

  注释：WHERE 是一个通用过滤子句，既可以用于单表查询，也可以用于多表连接后的结果过滤，位置在 FROM 子句之后。

(3)错误用法示例

-- 错误写法：JOIN 后缺少 ON 子句
SELECT *
FROM employees
JOIN departments
WHERE employees.dept_id = departments.id;  -- 这里错误地用 WHERE 代替了 ON

-- 正确写法：JOIN 后必须使用 ON 指定连接条件
SELECT *
FROM employees
JOIN departments 
    ON employees.dept_id = departments.id;  -- 正确使用 ON 定义连接关系

小结

• ON 是专门用于 JOIN 子句的关键字，必须紧跟在 JOIN 之后，用于定义表之间的连接规则。
• WHERE 是通用的过滤关键字，可独立使用或在连接后过滤结果，用于筛选符合条件的记录。
• 两者不能随意替换，JOIN 后必须使用 ON 而非 WHERE 来指定连接条件。

2、从作用时机与执行顺序来看

(1)作用时机

ON 的作用时机

• 发生在表连接阶段，用于在生成临时表时确定表之间的匹配关系。
• 当进行多表连接（如 JOIN）时，数据库会先根据 ON 后的条件筛选符合连接规则的记录，将匹配的行组合起来生成中间临时表。
• 对于 LEFT JOIN/RIGHT JOIN 等外连接，ON 条件不匹配的行（如左表中没有对应右表数据的行）会被保留（右表字段为 NULL），不会被过滤。

WHERE 的作用时机

• 发生在临时表生成之后，用于对已连接好的临时表进行最终过滤。
• 数据库先完成所有表的连接并生成临时表，再根据 WHERE 条件筛选临时表中符合要求的记录，不符合条件的行会被直接排除。

(2)执行顺序

SQL 查询的执行顺序中，ON 和 WHERE 的位置如下：
FROM → 表连接（JOIN）→ ON 条件筛选 → 生成临时表 → WHERE 条件过滤 → 后续操作（SELECT、GROUP BY 等）

简单说：ON先执行，WHERE后执行。

(3)对比示例

模拟数据：

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO departments VALUES
(1, '研发部'),
(2, '市场部'),
(3, '行政部');

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    salary INT
);
INSERT INTO employees VALUES
(101, '张三', 1, 8000),
(102, '李四', 2, 7000),
(103, '王五', NULL, 9000);

说明：

• departments（部门表）：包含 3 个部门（研发部、市场部、行政部）。
• employees（员工表）：3 名员工，其中“王五”的 dept_id 为 NULL（无所属部门）。

示例 1：LEFT JOIN + ON（仅连接条件）

SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.id; -- ON在连接时生效

执行过程：
① ON 条件 e.dept_id = d.id 先处理，匹配员工与部门：

• 张三（dept_id=1）→ 研发部（id=1）
• 李四（dept_id=2）→ 市场部（id=2）
• 王五（dept_id=NULL）→ 无匹配部门

② 生成临时表时，LEFT JOIN 特性保留左表（员工表）所有行，王五的部门字段为 NULL。

输出结果：

员工	部门
张三	研发部
李四	市场部
王五	NULL

示例 2：LEFT JOIN + ON + WHERE（连接后过滤）

SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.id
WHERE d.name = '研发部'; -- WHERE在临时表生成后生效

执行过程：
① 先执行 ON 条件，生成与示例 1 相同的临时表（包含张三、李四、王五）。
② 再执行 WHERE d.name = '研发部'，过滤临时表：

• 仅张三的部门是“研发部”，符合条件。
• 李四（市场部）和王五（NULL）被过滤。

输出结果：

员工	部门
张三	研发部

小结

① 执行顺序：ON 先于 WHERE 执行，ON 负责表连接时的匹配，WHERE 负责连接后对临时表的过滤。
② 外连接差异：在 LEFT JOIN/RIGHT JOIN 中，ON 不会过滤主表（左表/右表）的记录，而 WHERE 会过滤所有不符合条件的记录（包括主表中不匹配的行）。
③ 作用对象：ON 作用于连接过程中的表，WHERE 作用于连接后生成的临时表。

3、从适用场景来看

(1)ON 的核心场景

• 专门用于定义表之间的连接关系，是多表关联查询的必要条件。
• 主要作用：声明不同表之间的匹配规则（通常基于主外键关系），决定哪些行需要被连接在一起。
• 必须与 JOIN 系列关键字（INNER JOIN/LEFT JOIN/RIGHT JOIN 等）配合使用，无法单独出现。

(2)WHERE 的核心场景

• 用于筛选最终结果集，对数据进行条件过滤。
• 主要作用：从查询结果（单表数据或多表连接后的临时表）中提取符合条件的记录，可基于单个字段或多个字段的组合条件进行筛选。
• 既可单独用于单表查询，也可在多表连接后对结果进行二次过滤。

示例 1：表间关系定义（ON 的专属场景）
当需要连接多个表时，必须使用 ON 声明它们的关联规则，这是 ON 最核心的用途：

-- 正确：用 ON 定义员工表与部门表的关联关系（基于外键 dept_id）
SELECT e.name AS 员工, d.name AS 部门
FROM employees e
INNER JOIN departments d 
    ON e.dept_id = d.id; -- ON 明确两表如何关联

-- 错误：JOIN 后缺少 ON 会导致语法错误
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d 
    WHERE e.dept_id = d.id; -- WHERE 不能替代 ON 定义表间关系

说明：ON 在这里的作用是告诉数据库“员工表的 dept_id 与部门表的 id 相等时，这两行需要被连接”，是多表关联的基础。

示例 2：结果过滤（WHERE 的核心场景）场景 A：单表查询过滤无需连接其他表时，WHERE 可直接过滤单表数据：

-- 筛选工资大于 7500 的员工（单表过滤）
SELECT name, salary
FROM employees
WHERE salary > 7500; -- WHERE 直接过滤员工表数据

场景 B：多表连接后过滤连接表之后，WHERE 可对生成的临时表进一步筛选：

-- 先连接表，再筛选研发部中工资大于 7500 的员工
SELECT e.name AS 员工, d.name AS 部门, e.salary
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.id -- ON 定义连接关系
WHERE d.name = '研发部' AND e.salary > 7500; -- WHERE 过滤连接后的结果

说明：WHERE 在这里的作用是从“员工-部门”连接后的临时表中，提取“部门为研发部且工资>7500”的记录。

小结

① ON 是表连接的“粘合剂”：仅用于多表 JOIN 时定义关联规则，没有 ON 的 JOIN 会报错。
② WHERE 是结果集的“过滤器”：用于筛选数据，无论单表还是多表场景都能使用，作用于最终结果。
③ 使用原则：

• 需声明表之间的关联关系时，用 ON；
• 需筛选查询结果时，用 WHERE。

两者不可混淆，例如不能用 WHERE 代替 ON 定义表连接条件。

4、重点从对结果的影响来看

下面将通过模拟数据，详细拆解 SQL 中 ON 和 WHERE 对数据过滤结果的影响：

(1)模拟数据

我们使用三张表进行分析：

• departments（部门表）：存储部门 ID 和名称
• employees（员工表）：存储员工 ID、姓名、所属部门 ID 和薪资
• performance（绩效表）：存储员工绩效记录（员工 ID 和绩效分数）

-- 部门表数据
SELECT * FROM departments;
-- 结果：
-- id | name  
-- ----+-------
-- 1  | 研发部
-- 2  | 市场部
-- 3  | 行政部

-- 员工表数据
SELECT * FROM employees;
-- 结果：
-- id  | name | dept_id | salary
-- -----+------+---------+--------
-- 101 | 张三 | 1       | 8000
-- 102 | 李四 | 2       | 7000
-- 103 | 王五 | NULL    | 9000

-- 绩效表数据
SELECT * FROM performance;
-- 结果：
-- emp_id | score
-- -------+-------
-- 101    | 85
-- 102    | 92
-- 101    | 70

(2)LEFT JOIN 中 ON 与 WHERE 的过滤差异

左连接（LEFT JOIN）以左表为基准，ON 和 WHERE 对结果的影响差异显著：

场景 1：ON 条件过滤（连接时筛选）

-- 左连接时，在 ON 中添加过滤条件（部门名称为研发部）
SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.id  -- 基础连接条件：员工所属部门ID匹配部门表ID
    AND d.name = '研发部';  -- 额外过滤条件：只连接部门名称为研发部的记录

执行逻辑：
① 先根据 e.dept_id = d.id 匹配员工与部门
② 同时仅保留部门名称为“研发部”的匹配结果
③ 左表（员工表）所有记录均保留，右表（部门表）不匹配的记录字段置为 NULL

输出结果：

员工   | 部门  
-------+-------
张三   | 研发部  -- 匹配成功，部门为研发部
李四   | NULL   -- 部门为市场部，不满足ON条件，右表置NULL
王五   | NULL   -- 无所属部门，右表置NULL

场景 2：WHERE 条件过滤（连接后筛选）

-- 左连接后，在 WHERE 中添加过滤条件（部门名称为研发部）
SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.id  -- 仅保留基础连接条件
WHERE d.name = '研发部';  -- 对连接后的临时表过滤

执行逻辑：
① 先根据 e.dept_id = d.id 完成左连接，生成临时表（包含所有员工，部门字段匹配或为 NULL）
② 再对临时表应用 d.name = '研发部' 过滤，仅保留部门为研发部的记录

输出结果：

员工   | 部门  
-------+-------
张三   | 研发部  -- 仅保留满足WHERE条件的记录

主要差异：

• ON + LEFT JOIN：保留左表所有记录，右表不匹配则置 NULL
• WHERE：直接过滤掉所有不满足条件的记录（包括左表中不匹配的行）

(3)INNER JOIN 中 ON 与 WHERE 的过滤差异

内连接（INNER JOIN）仅保留两表匹配的记录，ON 和 WHERE 结果可能相同，但执行逻辑和效率不同：

场景 1：ON 中包含过滤条件（先过滤后连接）

-- 内连接时，在 ON 中添加绩效分数过滤条件
SELECT 
    e.name AS 员工姓名,
    p.score AS 绩效分数
FROM employees e
INNER JOIN performance p 
    ON e.id = p.emp_id  -- 基础连接条件：员工ID匹配绩效表员工ID
    AND p.score >= 90;  -- 额外过滤条件：仅连接绩效分数≥90的记录

执行逻辑：
① 先过滤绩效表（performance）中分数 <90 的记录（仅保留李四的 92 分）
② 再与员工表（employees）连接，仅保留匹配的记录

输出结果：

员工姓名 | 绩效分数
--------+---------
李四    | 92       -- 仅保留绩效≥90的记录

场景 2：WHERE 中包含过滤条件（先连接后过滤）

-- 内连接后，在 WHERE 中添加绩效分数过滤条件
SELECT 
    e.name AS 员工姓名,
    p.score AS 绩效分数
FROM employees e
INNER JOIN performance p 
    ON e.id = p.emp_id  -- 仅保留基础连接条件
WHERE p.score >= 90;  -- 对连接后的临时表过滤

执行逻辑：
① 先将员工表与绩效表完全连接（保留所有匹配的记录，包括张三的 85 分、70 分和李四的 92 分）
② 再对连接后的临时表过滤，仅保留绩效分数 ≥90 的记录

输出结果：

员工姓名 | 绩效分数
--------+---------
李四    | 92       -- 结果与场景1相同

效率差异分析虽然上述两种查询结果相同，但执行效率可能有差异，尤其是多表连接时：

-- 场景A：ON中过滤（先过滤后连接）
EXPLAIN ANALYZE
SELECT *
FROM employees e
JOIN performance p 
    ON e.id = p.emp_id 
    AND p.score > 80-- 先过滤绩效表，减少连接数据量
JOIN departments d 
    ON e.dept_id = d.id;

-- 执行计划显示：
-- 扫描绩效表时直接过滤掉score≤80的记录（仅保留85、92），再进行连接

-- 场景B：WHERE中过滤（先连接后过滤）
EXPLAIN ANALYZE
SELECT *
FROM employees e
JOIN performance p ON e.id = p.emp_id
JOIN departments d ON e.dept_id = d.id
WHERE p.score > 80;  -- 先连接所有数据，再过滤

-- 执行计划显示：
-- 先连接三张表（包括score=70的记录），再过滤，处理的数据量更大

小结：

• 内连接中，当过滤条件仅涉及关联字段时，ON 和 WHERE 结果一致
• 当过滤条件包含非关联字段（如绩效分数）时：
• ON 先过滤后连接，处理的数据量更小，效率更高
• WHERE 先连接后过滤，处理的数据量更大，可能更低效

(4)右连接（RIGHT JOIN）中ON与WHERE的过滤差异

右连接以右表为基准，会保留右表所有记录，左表不匹配的字段置为NULL。ON和WHERE的作用时机不同，对结果的影响也不同。

场景 1：ON 中包含过滤条件（先过滤后连接）使用employees（员工表）和departments（部门表）进行右连接：

-- 右连接时，在ON中添加过滤条件（员工薪资>7500）
SELECT 
    e.name AS 员工,
    d.name AS 部门,
    e.salary AS 薪资
FROM employees e
RIGHT JOIN departments d 
    ON e.dept_id = d.id  -- 基础连接条件：员工所属部门匹配部门ID
    AND e.salary > 7500;  -- 额外过滤条件：仅连接薪资>7500的员工

执行逻辑：
① 右表（departments）所有部门（研发部、市场部、行政部）均会保留
② 左表（employees）仅匹配“部门ID一致且薪资>7500”的员工
③ 左表不匹配的记录（如市场部的李四薪资7000、行政部无员工）字段置为NULL

输出结果：

员工   | 部门   | 薪资  
-------+--------+-------
张三   | 研发部 | 8000  -- 薪资>7500，匹配成功
NULL   | 市场部 | NULL  -- 李四薪资7000不满足ON条件，左表置NULL
NULL   | 行政部 | NULL  -- 无员工，左表置NULL

场景 2：WHERE 中包含过滤条件（先连接后过滤）

-- 右连接后，在WHERE中添加过滤条件（员工薪资>7500）
SELECT 
    e.name AS 员工,
    d.name AS 部门,
    e.salary AS 薪资
FROM employees e
RIGHT JOIN departments d 
    ON e.dept_id = d.id  -- 仅保留基础连接条件
WHERE e.salary > 7500;  -- 对连接后的临时表过滤

执行逻辑：
① 先完成右连接，生成临时表（保留所有部门，员工信息匹配或为NULL）
② 再对临时表应用e.salary > 7500过滤，仅保留薪资符合条件的记录

输出结果：

员工   | 部门   | 薪资  
-------+--------+-------
张三   | 研发部 | 8000  -- 仅保留满足WHERE条件的记录

主要差异：

• ON + RIGHT JOIN：保留右表所有记录，左表不匹配则置NULL
• WHERE：直接过滤掉所有不满足条件的记录（包括右表中不匹配的行）

(5)全连接（FULL JOIN）中ON与WHERE的过滤差异

全连接（FULL JOIN）会保留左右两表的所有记录，两表不匹配的字段分别置为 NULL。ON 和 WHERE 的作用时机不同，对结果的影响显著：

场景 1：ON 条件过滤（连接时筛选）

-- 全连接时，在 ON 中添加过滤条件（部门ID为1或2）
SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e
FULL JOIN departments d 
    ON e.dept_id = d.id  -- 基础连接条件：员工所属部门ID匹配部门表ID
    AND d.id IN (1, 2);  -- 额外过滤条件：仅连接部门ID为1或2的记录

执行逻辑：
① 先根据 e.dept_id = d.id 匹配员工与部门，同时仅保留部门ID为1（研发部）或2（市场部）的匹配结果

② 保留左表（员工表）所有记录：

• 张三（dept_id=1）匹配研发部
• 李四（dept_id=2）匹配市场部
• 王五（dept_id=NULL）无匹配部门，右表字段置 NULL

③ 保留右表（部门表）所有记录，但仅连接部门ID为1或2的记录：

• 行政部（id=3）不满足 ON 条件，左表字段置 NULL

输出结果：

员工   | 部门  
-------+-------
张三   | 研发部  -- 匹配成功（部门ID=1）
李四   | 市场部  -- 匹配成功（部门ID=2）
王五   | NULL   -- 员工表记录保留，无匹配部门
NULL   | 行政部  -- 部门表记录保留，不满足ON条件

场景 2：WHERE 条件过滤（连接后筛选）

-- 全连接后，在 WHERE 中添加过滤条件（部门ID为1或2）
SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e
FULL JOIN departments d 
    ON e.dept_id = d.id  -- 仅保留基础连接条件
WHERE d.id IN (1, 2);  -- 对连接后的临时表过滤

执行逻辑：
① 先完成全连接，生成临时表：

• 张三匹配研发部，李四匹配市场部，王五无匹配部门（右表 NULL）
• 行政部无匹配员工（左表 NULL）

② 再对临时表应用 d.id IN (1, 2) 过滤，仅保留部门ID为1或2的记录

输出结果：

员工   | 部门  
-------+-------
张三   | 研发部  -- 满足WHERE条件（部门ID=1）
李四   | 市场部  -- 满足WHERE条件（部门ID=2）

主要差异

场景	核心逻辑	结果特点
ON + FULL JOIN	连接时过滤，保留两表所有记录	不满足 ON 条件的记录仅对方表字段置 NULL
WHERE + FULL JOIN	连接后过滤，直接剔除不满足条件的记录	仅保留完全满足 WHERE 条件的记录

小结

• FULL JOIN 中，ON 条件仅影响连接过程，不影响两表本身的记录保留（左右表所有行均会出现）。
• WHERE 条件作用于连接后的临时表，会直接过滤掉所有不满足条件的记录，包括两表中原本应保留的不匹配行。
• 实际使用时，若需保留两表全部记录并仅对匹配关系过滤，用 ON；若需严格筛选最终结果，用 WHERE。

四种连接类型中ON与WHERE的过滤逻辑对结果的影响小结

连接类型	ON  条件过滤特点	WHERE  条件过滤特点	差异对比
LEFT JOIN	保留左表所有记录，右表仅保留满足 ON 条件的匹配结果，不匹配则置 NULL	对连接后的临时表过滤，仅保留满足条件的记录（左表不匹配的行被剔除）	ON  保留左表全部记录，WHERE 可能剔除左表不匹配行
RIGHT JOIN	保留右表所有记录，左表仅保留满足 ON 条件的匹配结果，不匹配则置 NULL	对连接后的临时表过滤，仅保留满足条件的记录（右表不匹配的行被剔除）	ON  保留右表全部记录，WHERE 可能剔除右表不匹配行
INNER JOIN	先过滤后连接，减少参与连接的数据量，效率更高（结果与 WHERE 可能一致）	先连接所有匹配记录，再过滤，处理数据量更大（结果与 ON 可能一致，但效率可能更低）	结果可能相同，但 ON 执行效率更高（尤其多表连接时）
FULL JOIN	保留左右两表所有记录，仅对匹配关系应用 ON 条件，不匹配则对方表字段置 NULL	对连接后的临时表过滤，仅保留满足条件的记录（左右表不匹配的行均可能被剔除）	ON  保留两表全部记录，WHERE 严格筛选最终结果，可能剔除大量不匹配行

通过以上分析可见：ON是连接阶段的筛选条件，决定表之间如何匹配；WHERE是连接后的筛选条件，决定最终保留哪些记录。实际使用时需根据连接类型和业务需求选择合适的关键字，避免因逻辑混淆导致结果错误。

四、对比与总结

1、综合对比表

	ON  子句	WHERE  子句
语法位置	只能用于 JOIN 操作中，紧跟在 JOIN 关键字后（如 LEFT JOIN ... ON ...）	可用于 SELECT、UPDATE、DELETE 等语句中，通常在 FROM 或 JOIN 之后
作用时机	在表连接时执行过滤，先过滤再连接	在表连接完成后执行过滤
适用场景	主要用于指定多表连接的条件（如关联字段匹配），也可过滤连接前的记录	用于过滤连接后整个结果集的记录，或单表查询时过滤记录
对过滤结果的影响	对于 LEFT JOIN/RIGHT JOIN，不满足 ON 条件的记录会保留（被连接表字段为 NULL）；对于 INNER JOIN，效果与 WHERE 类似	无论何种连接方式，不满足 WHERE 条件的记录都会被剔除
主要作用	定义表间连接逻辑（核心是关联规则），同时可在连接阶段过滤数据	过滤最终结果集（核心是筛选规则），包括单表或多表连接后的记录
效率影响	可提前过滤参与连接的表数据，减少连接时处理的数据量（尤其多表连接时效率更高）	需在连接完成后对临时表过滤，若连接后数据量大，可能导致效率降低

说明：
(1)主要作用差异：ON 的核心是“让表正确关联”，过滤是附加功能；WHERE 的核心是“留下符合条件的记录”，与关联逻辑无关。

(2)效率影响实例：若用 INNER JOIN 关联两张大表，ON 中添加非关联字段过滤（如 ON t1.id = t2.id AND t2.status = 1），会先筛出 t2 中 status=1 的记录再连接，比先连接再用 WHERE t2.status=1 处理的数据量更少，效率更高。

(2)“ON 先过滤再连接，WHERE 后过滤”的区别：简单说，ON 是“带着条件去连接”，WHERE 是“连接完了再筛”，这也是它们对结果影响不同的核心原因。

• ON 的过滤时机：在两张表进行连接（比如 JOIN）时，ON 条件会先对参与连接的表进行筛选，只保留符合条件的记录，然后再将这些筛选后的记录进行连接。
  举例：A 表和 B 表做 LEFT JOIN，ON 条件为 A.id = B.a_id AND B.age > 18。这时候会先从 B 表中过滤出 age > 18 的记录，再和 A 表中匹配的记录连接。即使 A 表有些记录在 B 表中没有符合条件的匹配，也会保留 A 表的记录（LEFT JOIN 特性）。

• WHERE 的过滤时机：无论是什么连接方式，都会先把两张表按照连接条件完整连接成一张临时表，然后 WHERE 条件再对这张临时表中的记录进行过滤，只保留符合条件的最终结果。
  举例：同样 A 表和 B 表做 LEFT JOIN，连接条件是 A.id = B.a_id，然后加 WHERE B.age > 18。这时候会先把 A 和 B 中所有匹配的记录连接（包括 B 表中 age ≤ 18 的），形成临时表后，再过滤掉 B.age ≤ 18 的记录，导致 A 表中那些在 B 表没有符合条件的记录也会被过滤掉（相当于变成了 INNER JOIN 的效果）。

2、终极总结

(1)基础规则：

• ON是连接的基石，WHERE是结果的筛子
• 所有JOIN操作必须使用ON，禁止用WHERE替代连接条件

(2)左/右连接场景：

• 用ON保留主表数据（不匹配时填充NULL）
• 用WHERE过滤会破坏外连接特性

(3)内连接优化：

• 关联字段过滤：ON/WHERE结果相同
• 非关联字段过滤：

  -- 优选方案（提前过滤大表）
  SELECT *
  FROM LargeTable
  JOIN SmallTable
      ON LargeTable.id = SmallTable.id
      AND LargeTable.size > 1000  -- 先过滤大表
  
  -- 次选方案（全连接后过滤）
  SELECT *
  FROM LargeTable
  JOIN SmallTable
      ON LargeTable.id = SmallTable.id
  WHERE LargeTable.size > 1000
  

(4)调试技巧：
当结果不符合预期时：

-- 步骤1：去掉WHERE子句观察中间结果
SELECT * FROM A LEFT JOIN B ON ...

-- 步骤2：逐步添加WHERE条件定位问题

通过以上详细示例和对比，可明确ON和WHERE的本质区别：ON构建表关系，WHERE决定最终输出。记住：ON是连接表时用的，WHERE是最后筛结果的。分清这俩，不仅查询结果准，跑起来还可能更快，赶紧用起来吧。

阅读原文：原文链接