MySQL 8中LEFT JOIN条件移至WHERE子句的查询差异问题

问题场景 #

在MySQL 8环境下执行以下两个LEFT JOIN查询，结果出现明显差异：

查询1：条件放在JOIN子句 #

SELECT pboi.id, pboi.status, pboi.expires_at, users.id, users.name, p.id, p.title
    FROM postponed_back_order_items pboi
    LEFT JOIN
        users ON users.id = pboi.creator_id   AND users.status = 'A'
    LEFT JOIN
        products AS p ON p.id = pboi.product_id  AND p.status = 'A'
    WHERE pboi.status = 'P'

该查询返回3行结果，其中包含1条products表中status≠'A'的数据（对应p.id、p.title为NULL）。

查询2：条件移至WHERE子句 #

SELECT pboi.id, pboi.status, pboi.expires_at, users.id, users.name, p.id, p.title
    FROM postponed_back_order_items pboi
    LEFT JOIN
        users ON users.id = pboi.creator_id   AND users.status = 'A'
    LEFT JOIN
        products AS p ON p.id = pboi.product_id
    WHERE pboi.status = 'P'  AND p.status = 'A'

该查询仅返回2行结果，排除了products表中status≠'A'的数据。

关联的三张表结构如下：

CREATE TABLE `postponed_back_order_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `creator_id` bigint unsigned NOT NULL,
  `order_id` bigint unsigned NOT NULL,
  `product_id` bigint unsigned NOT NULL,
  `status` enum('I','C','P','O','R') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'I-Invoice, C-Cancelled, P-Processing, O - Completed, R - Refunded',
  `expires_at` datetime NOT NULL,
  `qty` int unsigned NOT NULL,
  `price` int unsigned NOT NULL COMMENT 'Cast on client must be used - Money sum = value/100',
  `total_price` decimal(8,2) GENERATED ALWAYS AS ((`price` * `qty`)) STORED COMMENT 'Cast on client must be used - Money sum = value/100',
  `manager_id` bigint unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `status` enum('N','A','I','B') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT ' N => New(Waiting activation), A=>Active, I=>Inactive, B=>Banned',
  `membership_mark` enum('N','M','S','G') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT ' N => No membership, M - Member, S=>Silver Membership, G=>Gold Membership',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `creator_id` bigint unsigned NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('D','P','A','I') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'D' COMMENT ' D => Draft, P=>Pending Review, A=>Active, I=>Inactive',
  `slug` varchar(260) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sku` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sale_price` int unsigned DEFAULT NULL,
  `in_stock` tinyint(1) NOT NULL DEFAULT '0',
  `stock_qty` mediumint unsigned NOT NULL DEFAULT '0',
  `discount_price_allowed` tinyint(1) NOT NULL DEFAULT '0',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `short_description` mediumtext COLLATE utf8mb4_unicode_ci,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `published_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

原因解析 #

核心差异来自LEFT JOIN的ON子句和WHERE子句的执行逻辑与时机不同：

• ON子句的逻辑：
  LEFT JOIN的本质是保留左表（postponed_back_order_items）所有符合WHERE条件的行。ON子句的条件仅用于筛选右表（products）中能匹配的行——如果右表没有满足p.id = pboi.product_id AND p.status = 'A'的记录，左表的行依然会被保留，只是右表对应的字段会填充为NULL。这就是第一个查询能返回3行的原因：那行products状态非A的记录，虽然没匹配到有效products数据，但pboi的行仍然被保留。

• WHERE子句的逻辑：
  WHERE子句是在所有JOIN操作完成后，对最终的结果集进行筛选。当把p.status = 'A'移到WHERE子句时，对于那行没有匹配到有效products的记录，p.status的值是NULL，而NULL = 'A'的判断结果为假，所以这行会被WHERE条件过滤掉，最终只返回2行符合要求的记录。

内容的提问来源于stack exchange，提问作者mstdmstd