求助：查询无指定日期后状态为0的服务的客户/位置

现有三张表结构及关联关系：

• client表：

+----+------+-------+
| id | name | phone |
+----+------+-------+

每个客户对应一个或多个位置；

• location表：

+----+------+-------+---------+
| id | name | phone | address | 
+----+------+-------+---------+

每个位置对应一项或多项服务；

• services表（关联client与location）：

+----+-----------+-------------+------+--------+
| id | client_id | location_id | date | status |
+----+-----------+-------------+------+--------+

需求：基于services表的datetime类型字段date和int类型字段status过滤，获取指定日期之后没有状态为0的服务的客户/位置列表。

尝试以下查询语句返回空结果，但实际存在符合条件的位置：

SELECT `id`,`name` FROM `location` WHERE `id` NOT IN (SELECT `location_id` FROM `services` WHERE `date` > '2022-09-01 00:00:00' AND `status` = 0 ) 

核心问题 #

NOT IN子查询如果返回的结果集中包含NULL值，会导致整个条件逻辑判定为UNKNOWN，最终无法筛选出任何结果。同时原语句未考虑从未有过服务记录的位置（这类位置也符合“指定日期后无状态0服务”的要求）。

方案1：使用NOT EXISTS替代NOT IN #

此方式不受NULL值影响，同时能覆盖无服务记录的位置：

SELECT l.id, l.name
FROM location l
WHERE NOT EXISTS (
    SELECT 1
    FROM services s
    WHERE s.location_id = l.id
      AND s.date > '2022-09-01 00:00:00'
      AND s.status = 0
)

方案2：使用LEFT JOIN + IS NULL #

同样能规避NULL问题，逻辑更直观：

SELECT l.id, l.name
FROM location l
LEFT JOIN services s
    ON s.location_id = l.id
    AND s.date > '2022-09-01 00:00:00'
    AND s.status = 0
WHERE s.id IS NULL

扩展：关联client表获取客户信息 #

如果需要同时获取符合条件的客户数据，可使用以下查询：

-- 获取符合条件的客户列表
SELECT c.id, c.name
FROM client c
WHERE NOT EXISTS (
    SELECT 1
    FROM location l
    JOIN services s ON s.location_id = l.id
    WHERE l.client_id = c.id
      AND s.date > '2022-09-01 00:00:00'
      AND s.status = 0
)

-- 获取客户+位置的关联列表
SELECT c.id AS client_id, c.name AS client_name, l.id AS location_id, l.name AS location_name
FROM client c
JOIN location l ON l.client_id = c.id
WHERE NOT EXISTS (
    SELECT 1
    FROM services s
    WHERE s.location_id = l.id
      AND s.date > '2022-09-01 00:00:00'
      AND s.status = 0
)

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