如何在不使用子查询的情况下获取带is_read标识的客户通知列表？

要实现获取指定客户的所有通知并添加is_read标识，同时避免子查询，正确的做法是将client_id的筛选条件放在LEFT JOIN的ON子句中，而非WHERE子句里。具体SQL如下：

SELECT 
    n.id,
    n.meta,
    n.status,
    n.created_at,
    n.updated_at,
    CASE WHEN c.client_id IS NOT NULL THEN 1 ELSE 0 END AS is_read
FROM my_notification n
LEFT JOIN my_client_notification c
    ON n.id = c.notification_id 
    AND c.client_id = [指定的客户ID]; -- 替换为实际的客户ID值

为什么之前的写法会丢失行？ #

如果把c.client_id = [指定客户ID]放在WHERE子句中，LEFT JOIN返回的未被阅读的通知行里，c.client_id的值为NULL，WHERE条件会直接过滤掉这些NULL行，最终只返回该客户已阅读的通知，丢失未阅读部分。

将筛选条件放到ON子句时，LEFT JOIN会先按n.id = c.notification_id和c.client_id = 指定值进行关联，未匹配到的通知行依然会被保留，只是关联表的字段值为NULL，此时就能通过CASE语句正确生成is_read标识。

补充说明 #

• 该写法利用MySQL 8.0的LEFT JOIN特性，完全无需子查询即可实现需求。
• 若需额外过滤通知状态（如仅显示status=1的有效通知），可在WHERE子句中添加n.status = 1，不会影响未阅读通知的返回。

内容的提问来源于stack exchange，提问作者sergei.sss