SQL多表关联需保留某一表全部记录的实现问题

因操作限制需在SQL中完成复杂关联，现有三个表两两存在可关联公共列，但无三表共有的关联字段，表结构如下：

Table 1 #

Table 2 #

Table 3 #

需求 #

关联后输出包含rule_type、code、site_ref、population字段，以及Table 1中对应记录的计数，期望输出如下：

尝试的问题 #

我尝试通过FULL OUTER JOIN关联，SQL语句如下：

SELECT code, count(*) as count, site_ref, population, rule_type, population
FROM 
  (SELECT A.code, count(*) as count, B.site_ref, C.population, A.rule_type
   FROM table_1 as A
     FULL OUTER JOIN table_2 AS B ON A.code = B.code
     JOIN table_3 as C ON B.site_ref = C.site_ref
   WHERE site_ref = 'XYZ' AND rule_type in ('Type A', 'Type B', 'Type C')) 
GROUP BY code, count(*) as count, site_ref, population, rule_type, population

但返回结果缺少Type B的记录，仅输出Type A和Type C的条目。原因是Table 2中无Type B对应的code，导致该记录被过滤，FULL OUTER JOIN未起到保留所有rule_type的作用。

解决方案 #

问题根源 #

1. FULL OUTER JOIN后使用了INNER JOIN关联Table3，会过滤掉Table1中无对应Table2记录的行（Type B的site_ref为NULL，无法匹配Table3的site_ref）；
2. WHERE条件中的site_ref = 'XYZ'进一步过滤了site_ref为NULL的行；
3. 子查询与外层重复使用count(*)和GROUP BY，逻辑混乱导致计数错误。

正确SQL语句 #

SELECT 
    t1.rule_type,
    t1.code,
    t3.site_ref,
    t3.population,
    CASE WHEN t2.code IS NOT NULL THEN t1.total_count ELSE 0 END AS count
FROM (
    -- 聚合Table1中每个规则类型+代码的记录数
    SELECT rule_type, code, COUNT(*) AS total_count
    FROM table_1
    WHERE rule_type IN ('Type A', 'Type B', 'Type C')
    GROUP BY rule_type, code
) t1
-- 左连接Table2中site_ref为XYZ的唯一代码
LEFT JOIN (
    SELECT DISTINCT code
    FROM table_2
    WHERE site_ref = 'XYZ'
) t2 ON t1.code = t2.code
-- 左连接Table3中site_ref为XYZ的唯一记录（去重避免重复关联）
LEFT JOIN (
    SELECT DISTINCT site_ref, population
    FROM table_3
    WHERE site_ref = 'XYZ'
) t3 ON 1=1
ORDER BY t1.rule_type;

逻辑说明 #

1. 先聚合Table1，得到每个rule_type+code组合的总记录数，确保所有规则类型都被保留；
2. 左连接Table2中site_ref='XYZ'的唯一代码，判断该代码是否存在于目标站点的记录中；
3. 左连接Table3中site_ref='XYZ'的唯一记录，确保所有条目都能关联到目标站点的人口数据；
4. 使用CASE语句，当代码存在于Table2的目标站点记录中时显示Table1的计数，否则显示0。

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