如何编写SQL筛选满足特定关联条件的table_a记录

问题背景 #

现有三张表 table_a、table_b、table_c，关联关系为table_a通过table_b关联到table_c。table_b和table_c均包含布尔类型的active字段。需要编写SQL语句筛选table_a的记录，满足以下任一条件：

• 该table_a记录无关联的table_b记录
• 所有关联的table_b记录对应的table_c的active字段不为true（包括table_c的active为false，或该table_b记录无对应table_c记录）

预期筛选结果为table_a中id为1、2、5的记录。

解决方案 #

以下提供两种可行的SQL实现思路：

方法1：使用NOT EXISTS排除不符合条件的记录

通过子查询找出存在不符合条件关联的记录，再反向筛选目标结果：

SELECT a.*
FROM table_a a
WHERE 
  -- 匹配无关联table_b的记录
  NOT EXISTS (SELECT 1 FROM table_b b WHERE b.a_id = a.id)
  OR
  -- 匹配所有关联table_b对应的table_c.active都不为true的记录
  NOT EXISTS (
    SELECT 1
    FROM table_b b
    LEFT JOIN table_c c ON b.c_id = c.id
    WHERE b.a_id = a.id
    AND c.active = true
  );

方法2：分组后通过聚合函数判断全局条件

通过左连接关联所有表，分组后检查分组内是否存在不符合条件的记录：

SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
LEFT JOIN table_c c ON b.c_id = c.id
GROUP BY a.id
HAVING 
  -- 匹配无关联table_b的记录
  COUNT(b.id) = 0
  OR
  -- 匹配所有关联的table_c.active都不为true的记录
  MAX(CASE WHEN c.active = true THEN 1 ELSE 0 END) = 0;

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