我们写 SQL 时，是不是总搞混 JOIN 里的 ON...AND 和 ON...WHERE？别字面上看 JOIN...ON...AND 与 JOIN...ON...WHERE，看似差别细微，就只差个关键词，结果可能天差地别，实则会直接影响 SQL 查询结果的完整性、语义正确性和性能效率。比如：想保留左表所有数据，放错地方就可能丢行；算库存、销量时，还可能统计重复。我们要想把这个问题彻底搞明白，首先我们要搞懂 SQL 的执行顺序：ON 是连接时就筛，只影响要不要关联；WHERE 是连接完再筛，会砍最终结果。下面我们就从 SQL 查询的逻辑执行顺序开始，用实际例子掰扯清楚，帮我们避坑。

一、SQL 查询的逻辑执行顺序决定本质差异

SQL 查询的逻辑执行顺序是理解 JOIN...ON...AND 与 JOIN...ON...WHERE 二者区别的切入口，SQL 查询的逻辑执行顺序如下：

1、FROM/JOIN：确定参与查询的表，执行表连接；
2、ON：在连接过程中，根据条件筛选匹配的行（仅作用于连接阶段）；
3、WHERE：对连接生成的临时结果集进行全局过滤（作用于连接后的所有数据）；
4、GROUP BY/HAVING/SELECT/ORDER BY/LIMIT：后续数据处理。

简言之：

• ON 是“连接条件”：决定“哪些行能参与连接”，仅在表关联时生效，不影响左表（尤其是 LEFT JOIN）的完整性。
• WHERE 是“结果过滤”：决定“连接后保留哪些行”，会过滤掉不符合条件的所有行，包括左表中未匹配的记录。

二、以 LEFT JOIN 为关键场景对比 ON...AND 与 ON...WHERE

LEFT JOIN 的中心语义是“保留左表所有行，右表匹配则显示，不匹配则为 NULL”。ON...AND 与 ON...WHERE 在这一场景下的差异最为显著，以下我们通过模拟数据来验证。

1、创建模拟数据表并插入模拟数据

创建两张表：products（产品表）和 inventory（库存表），用在后面的查询测试。

-- 产品表：左表，需保留所有记录
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(20),
    size VARCHAR(5)
);
INSERT INTO products VALUES
(1, '纯棉衬衫', 'M'),
(2, '休闲长裤', 'L'),
(3, '防风夹克', 'M'),
(4, '棒球帽', 'S');

-- 库存表：右表，存储不同品牌的库存信息
CREATE TABLE inventory (
    inventory_id INT PRIMARY KEY,
    brand VARCHAR(10),
    size VARCHAR(5),
    stock_quantity INT
);
INSERT INTO inventory VALUES
(1, 'AAA', 'M', 50),
(2, 'BBB', 'L', 30),
(3, 'AAA', 'S', 20),
(4, 'CCC', 'M', 10);

表数据预览：
products（左表）：

product_id	product_name	size
1	纯棉衬衫	M
2	休闲长裤	L
3	防风夹克	M
4	棒球帽	S

inventory（右表）：

inventory_id	brand	size	stock_quantity
1	AAA	M	50
2	BBB	L	30
3	AAA	S	20
4	CCC	M	10

2、场景1：JOIN...ON...AND（连接时筛选右表）

需求：保留所有产品，仅关联“AAA 品牌”的库存记录（右表筛选条件放 ON 中）。

SELECT 
    p.product_id,
    p.product_name,
    p.size,
    i.brand,
    i.stock_quantity
FROM products p
LEFT JOIN inventory i 
    ON p.size = i.size  -- 基础连接条件：尺寸匹配
    AND i.brand = 'AAA';  -- 右表筛选条件：仅关联 AAA 品牌

执行逻辑：

• 先做 LEFT JOIN，匹配条件是：p.size = i.size并且i.brand = 'AAA'
• 即只有当右表 inventory 记录同时满足尺寸相同 且品牌为 'AAA' 时才连接，仅筛选出 brand = 'AAA' 的记录（即 inventory_id=1、3）
• 左表 products 所有行都会保留，不匹配则右表字段为 NULL。

⚠️ 关联重复风险说明：左表可能存在多行匹配右表单行的情况（即 “一对多” 或 “多对一” 关联），我们需警惕数据重复风险：本例中，左表 products 有 2 行（product_id=1 纯棉衬衫、product_id=3 防风夹克）均满足 size=M，且右表 inventory 中仅 inventory_id=1（AAA 品牌、M 码）符合条件，因此这 2 行产品会 重复匹配右表同一行数据（均显示 stock_quantity=50）。若后续对右表字段进行聚合计算（如：SUM(i.stock_quantity)），会导致重复统计（产品 1 和 3 合计 100，但实际 AAA 品牌 M 码库存仅 50），我们需通过 DISTINCT（如：SUM(DISTINCT i.stock_quantity)）或调整关联粒度（如：按 “产品 - 品牌 - 尺寸” 唯一键关联）规避该问题。

查询结果：

product_id	product_name	size	brand	stock_quantity
1	纯棉衬衫	M	AAA	50
2	休闲长裤	L	NULL	NULL
3	防风夹克	M	AAA	50
4	棒球帽	S	AAA	20

解析：（1）纯棉衬衫(M) → 匹配 inventory 中 AAA(M)，成功；（2）2休闲长裤(L) → 虽然 inventory 有 BBB(L)，但 brand 不是 AAA → 不匹配 → NULL；（3）防风夹克(M) → 同样匹配 AAA(M)；（4）棒球帽(S) → 匹配 AAA(S)。

关键结论：

• 左表所有行（4 行）均保留，符合 LEFT JOIN 语义；
• 右表仅关联符合 brand = 'AAA' 的记录，休闲长裤（L 码）无 AAA 品牌库存，故右表字段为 NULL。

3、场景2：JOIN...ON...WHERE（连接后筛选结果）

需求：保留所有产品，关联所有尺寸匹配的库存后，再筛选“AAA 品牌”的记录（右表筛选条件放 WHERE 中）。

SELECT 
    p.product_id,
    p.product_name,
    p.size,
    i.brand,
    i.stock_quantity
FROM products p
LEFT JOIN inventory i 
    ON p.size = i.size  -- 基础连接条件：仅尺寸匹配
WHERE i.brand = 'AAA';  -- 连接后筛选：仅保留 AAA 品牌

执行逻辑：
1、先按 size 关联左表和右表，生成临时结果集（包含所有尺寸匹配的记录，如：休闲长裤匹配 BBB 品牌）； 2、对临时结果集应用 WHERE i.brand = 'AAA'，过滤掉不符合条件的行（包括右表字段为 NULL 的行）。

查询结果：

product_id	product_name	size	brand	stock_quantity
1	纯棉衬衫	M	AAA	50
3	防风夹克	M	AAA	50
4	棒球帽	S	AAA	20

关键结论：
左表中的“休闲长裤”（product_id=2）被过滤，违背  LEFT JOIN “保留左表所有行”的语义；此时查询等价于 INNER JOIN，因为 WHERE 过滤了右表为 NULL 的行，失去外连接意义。继续上面的例子，我们看看 休闲长裤 发生了什么：

• 在 ON ... AND 查询中：休闲长裤(L) → 匹配到 BBB(L)，但 BBB ≠ AAA → 不连接 → 右表为 NULL，但 左表仍显示。
• 在 ON ... WHERE 查询中：休闲长裤(L) → 匹配 BBB(L)，右表 brand = 'BBB'；但 WHERE i.brand = 'AAA' → 条件不满足 → 整行被过滤；所以 休闲长裤 彻底消失。

这就是我们为什么说：在 LEFT JOIN 中，把过滤条件放在 WHERE 里，可能会让 LEFT JOIN 变成事实上的 INNER JOIN。

4、JOIN...ON...AND 与 JOIN...ON...WHERE 对比表

对比项	JOIN...ON...AND	JOIN...ON...WHERE
执行时机	连接阶段（匹配前筛选右表）	过滤阶段（连接后筛选临时结果集）
左表保留	保留左表所有行（符合 LEFT JOIN 语义）	可能过滤左表行（违背 LEFT JOIN 语义）
NULL 处理	允许右表字段为 NULL（不匹配时）	过滤右表字段为 NULL 的行
适用场景	需保留左表，仅关联右表特定记录	连接后需全局筛选（可接受左表行被过滤）
语义等价性	严格遵循 LEFT JOIN 语义	等价于 INNER JOIN（右表筛选时）

由此可见，ON ... AND 的右表条件仅过滤右表，不影响左表完整性（保留所有左表行，右表不匹配则为 NULL）。ON ... WHERE 的右表条件会过滤最终结果，可能导致左表行丢失（尤其当右表字段为 NULL 时），使 LEFT JOIN 退化为 INNER JOIN。

5、怎样正确使用 JOIN...ON...AND 与 JOIN...ON...WHERE？

场景1：我们只想连接右表中 brand='AAA' 的记录（保留左表）

LEFT JOIN inventory ON (products.size = inventory.size AND inventory.brand = 'AAA')
-- 正确：连接时限制，左表全保留

场景2：连接所有 size 匹配的记录，但最终我们只看 brand='AAA' 的结果

LEFT JOIN inventory ON (products.size = inventory.size)
WHERE inventory.brand = 'AAA'
-- ⚠️ 实际上变成了 INNER JOIN 效果

如果我们真的只想看右表为 AAA 的结果，且不关心左表未匹配的行，那我们不如直接用 INNER JOIN。

场景3：想保留左表，但右表字段可为空，我们只关心右表 brand='AAA' 或为空的情况

LEFT JOIN inventory ON (products.size = inventory.size)
WHERE inventory.brand = 'AAA' OR inventory.brand IS NULL
-- 可以保留部分 NULL 行，但逻辑复杂

场景4：INNER JOIN 中 ON 和 WHERE 效果一致

-- INNER JOIN 下，ON 和 WHERE 效果基本相同
SELECT *
FROM products
INNER JOIN inventory ON products.size = inventory.size
WHERE inventory.brand = 'AAA';

等价于：

SELECT *
FROM products
INNER JOIN inventory ON (products.size = inventory.size AND inventory.brand = 'AAA');

因为 INNER JOIN 本来就不保留不匹配的行，所以 ON 和 WHERE 的过滤效果一致。虽然在 INNER JOIN 中，ON ... AND 和 ON ... WHERE逻辑等价（因为不匹配的行都会被丢弃），在 INNER JOIN 中我们仍推荐将关联条件放 ON，业务过滤条件放 WHERE，以提升可读性。

6、小结：一句话口诀

（1）ON 是“连接条件”，决定如何匹配，即：ON 条件决定哪些行参与连接（连接时生效）；
（2）WHERE 是“结果过滤”，决定最终展示哪些行，即：WHERE 条件决定最终结果保留哪些行（连接后生效）；

LEFT JOIN inventory ON p.size = i.size AND i.brand = 'AAA'  -- 连接时过滤右表
WHERE p.size = 'M'  -- 连接后过滤左表（仍保留左表所有 M 码产品）

（3）LEFT JOIN 中，把右表条件放进 WHERE，等于放弃了“左表全保留”的特性！

三、实战场景中 JOIN 的正确用法

在复杂业务场景中，JOIN 常结合多表关联、聚合函数（如：SUM/COUNT）和性能调优，以下我们一起来拆解有关场景中 JOIN 的正确用法。

场景1：多表 LEFT JOIN + 多条件 ON...AND

需求：保留所有产品，关联“AAA 品牌且库存>30”的库存记录，同时关联产品的销售数据（仅 2024 年订单）。

-- 新增销售表
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    sale_quantity INT
);
INSERT INTO sales VALUES
(1, 1, '2024-01-05', 10),
(2, 2, '2023-12-20', 5),
(3, 3, '2024-02-10', 8),
(4, 4, '2024-03-15', 12);

-- 多表 LEFT JOIN，ON 中包含多个筛选条件
SELECT 
    p.product_id,
    p.product_name,
    i.brand,
    i.stock_quantity,
    COALESCE(SUM(s.sale_quantity), 0) AS total_sales_2024
FROM products p
LEFT JOIN inventory i 
    ON p.size = i.size 
    AND i.brand = 'AAA'  -- 右表1筛选：AAA品牌
    AND i.stock_quantity > 30  -- 右表1筛选：库存>30
LEFT JOIN sales s 
    ON p.product_id = s.product_id 
    AND s.sale_date >= '2024-01-01'  -- 右表2筛选：2024年订单
GROUP BY p.product_id, p.product_name, i.brand, i.stock_quantity;

解析：

• 左表 products 所有行保留；
• 库存表仅关联“AAA 品牌且库存>30”的记录（仅 inventory_id=1）；
• 销售表仅关联“2024 年订单”，若左表某产品（如：product_id=2，休闲长裤）在销售表中的订单不符合“2024 年”这个条件，s.sale_quantity 为 NULL，SUM(NULL) 结果为 NULL，COALESCE 将其转为 0。

场景2：聚合函数 + HAVING（避免 WHERE 过滤左表）

需求：保留所有产品，统计“AAA 品牌库存”，并筛选出总库存>40 的产品。

-- 错误写法：WHERE 过滤右表，导致左表行丢失
SELECT 
    p.product_name,
    SUM(i.stock_quantity) AS total_aaa_stock
FROM products p
LEFT JOIN inventory i 
    ON p.size = i.size
WHERE i.brand = 'AAA'  -- 错误：过滤掉无 AAA 库存的产品
GROUP BY p.product_name;

-- 正确写法：ON 筛选右表，HAVING 过滤聚合结果
SELECT 
    p.product_name,
    COALESCE(SUM(i.stock_quantity), 0) AS total_aaa_stock
FROM products p
LEFT JOIN inventory i 
    ON p.size = i.size 
    AND i.brand = 'AAA'  -- 正确：连接时筛选右表
GROUP BY p.product_name
HAVING COALESCE(SUM(i.stock_quantity), 0) > 40;  -- 筛选聚合结果

说明：本例中 SUM(i.stock_quantity) 我们需注意：若左表多产品匹配右表同一库存记录，我们需用 SUM(DISTINCT i.stock_quantity) （或提前聚合右表）避免重复统计（如：产品 1 和 3 均匹配 inventory_id=1 时，SUM 会默认计算 50+50=100，DISTINCT 可修正为 50）。

关键区别：

• 错误写法用 WHERE 过滤右表，导致“休闲长裤”等无 AAA 库存的产品被排除；
• 正确写法用 ON 筛选右表，HAVING 仅过滤聚合后的结果（如：总库存>40），保留左表所有行。

场景 3：性能优化（ON 提前过滤，减少连接数据量）

在数据量大的场景下，ON...AND 可提前筛选右表，减少参与连接的数据量，提升性能。

但我们需注意：现代优化器（如：SQL Server、Oracle）可能自动将 WHERE 条件下推到 JOIN 阶段（但不保证）。显式使用 ON ... AND 能确保提前过滤，尤其在复杂查询中更可靠。

-- 优化前：先连接所有数据，再 WHERE 过滤（处理数据量更大）
SELECT 
    o.order_id,
    c.customer_name,
    o.order_amount
FROM orders o
LEFT JOIN customers c 
    ON o.customer_id = c.customer_id
WHERE c.country = 'China';  -- 连接后过滤，处理更多数据

-- 优化后：ON 提前筛选右表，减少连接数据量
SELECT 
    o.order_id,
    c.customer_name,
    o.order_amount
FROM orders o
LEFT JOIN customers c 
    ON o.customer_id = c.customer_id 
    AND c.country = 'China'  -- 提前筛选，仅连接中国客户
WHERE c.customer_id IS NOT NULL;  -- 可选：保留有匹配的行

性能差异：

• 优化前：需连接所有客户数据，再过滤中国客户，临时结果集更大；
• 优化后：仅连接中国客户数据，减少连接开销，尤其适合右表（customers）数据量大的场景。

四、JOIN 的前沿应用：OLAP、云数据库与图数据库

在现代数据架构中，JOIN 的逻辑需结合数据库特性调整，以下是 JOIN 在有关场景下的适配方案。

1、OLAP 引擎（Presto/Dremio）：分布式 JOIN 优化

OLAP 引擎处理大规模数据时，ON...AND 可配合“广播小表”策略进一步优化：

-- Presto 中，小表（inventory）会被广播到各个节点，ON 提前筛选减少数据传输
SELECT 
    p.product_id,
    SUM(i.stock_quantity) AS total_stock
FROM products p
LEFT JOIN inventory i 
    ON p.size = i.size 
    AND i.brand = 'AAA'  -- 提前筛选，减少广播数据量
GROUP BY p.product_id;

2、云原生数据库（Snowflake）：自动聚簇与 JOIN 优化

Snowflake 等云数据库会自动优化 JOIN 执行计划，但 ON...AND 的语义正确性仍需人工保证：

-- Snowflake 自动选择最优 JOIN 策略，但 ON 条件需正确表达语义
SELECT 
    p.product_name,
    i.brand,
    i.stock_quantity
FROM products p
LEFT JOIN inventory i 
    ON p.size = i.size 
    AND i.brand = 'AAA'  -- 确保左表保留，避免 WHERE 过滤
WHERE p.size = 'M';  -- 左表筛选放 WHERE，不影响语义

3、图数据库（Neo4j）：用图遍历替代传统 JOIN

图数据库中无传统 JOIN 语法，需通过“节点关系遍历”实现类似逻辑，筛选条件类似 ON...AND：

-- Neo4j 图遍历：匹配“用户-下单-已完成订单”的关系，类似传统 JOIN 中 ON 筛选
MATCH (u:User)-[:PLACED]->(o:Order)
WHERE u.age > 20  -- 类似传统 JOIN 中左表筛选（WHERE）
  AND o.status = 'completed'  -- 类似传统 JOIN 中右表筛选（ON...AND）
RETURN u.name, o.order_amount;

说明：MATCH (u:User)-[:PLACED]->(o:Order) 遍历「用户 - 下单 - 订单」的节点关系，其中：u.age > 20 是对「用户节点」的属性筛选，o.status = 'completed' 是对「订单节点」的属性筛选，二者均作用于节点匹配阶段，类似传统 JOIN 中对左表、右表的 ON...AND 筛选（仅符合条件的节点才会参与关系遍历）。

总结：

写 JOIN 查询时，按以下逻辑选择 ON...AND 或 ON...WHERE：

1、若使用 LEFT JOIN，且需保留左表所有行 → 右表筛选条件放 ON...AND；
2、若使用 INNER JOIN → ON...AND 与 ON...WHERE 等价（建议关联条件放 ON，筛选条件放 WHERE，提升可读性）；
3、若需连接后全局筛选（可接受左表行丢失） → 筛选条件放 WHERE；
4、若需筛选聚合结果（如：SUM/COUNT 后） → 用 HAVING 替代 WHERE，避免过滤左表。

最后，我们要记住这儿最重要的原则：ON管“连接匹配”，WHERE管“结果保留”，LEFT JOIN中右表筛选别放WHERE。