MySQL优化方案咨询：如何筛选所有服务均已删除的客户

现有表结构 #

create table client (
    id INT NOT NULL,
    name varchar(100) NOT NULL,
    PRIMARY KEY ( id )
);

create table service (
    id INT NOT NULL,
    client_id INT NOT NULL,
    status varchar(36) NOT NULL,
    PRIMARY KEY ( id )
);

需求 #

筛选出所有服务状态**均为'DELETED'**的客户（默认包含无服务记录的客户，若需仅保留有服务的客户可调整方案）

当前尝试的问题 #

你当前的查询仅能返回client_id，无法获取客户的完整信息，且子查询逻辑可简化：

SELECT DISTINCT service.client_id
FROM service
WHERE service.id NOT IN (SELECT service.id FROM service s WHERE s.status <> 'DELETED' AND s.client_id = service.client_id); 

最优实现方案 #

方案1：LEFT JOIN排除法（高效易读） #

直接关联client表，排除存在非'DELETED'服务的客户，同时包含无服务的客户：

SELECT c.id, c.name
FROM client c
LEFT JOIN service s ON c.id = s.client_id AND s.status <> 'DELETED'
WHERE s.id IS NULL;

逻辑：左连接匹配所有有非'DELETED'服务的客户，最终筛选出未匹配到的客户——这些客户要么没有服务，要么所有服务都是'DELETED'。

方案2：NOT EXISTS子查询（数据库优化友好） #

多数数据库对NOT EXISTS的优化效果较好，逻辑直接清晰：

SELECT c.id, c.name
FROM client c
WHERE NOT EXISTS (
    SELECT 1 
    FROM service s 
    WHERE s.client_id = c.id 
    AND s.status <> 'DELETED'
);

逻辑：检查客户是否存在非'DELETED'的服务，不存在则符合条件。

方案3：分组统计验证（适用于需确认服务存在的场景） #

如果要求客户至少有一个服务且所有服务都是'DELETED'，可使用分组统计：

SELECT c.id, c.name
FROM client c
JOIN service s ON c.id = s.client_id
GROUP BY c.id, c.name
HAVING COUNT(CASE WHEN s.status <> 'DELETED' THEN 1 END) = 0;

逻辑：按客户分组后，统计非'DELETED'服务的数量，数量为0则所有服务均为'DELETED'。若要包含无服务的客户，将JOIN改为LEFT JOIN即可。

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