如何通过Dummy表与Full Outer Join补全候选人缺失的学历记录

现有#candidate表存储候选人的学历信息，部分候选人只拥有Higher Eduation（高等教育）、只拥有Postgrad Education（研究生教育），或者两者都具备。我们需要生成包含候选人缺失学历类型的补充记录，要求通过构建#dummytable，使用Full Outer Join或Outer Apply来实现，而非手动补全缺失项。

原表结构与测试数据 #

drop table if exists #candidate

create table #candidate
(
row_id int
,candidate_ref varchar(10)  
,qualification_type varchar(30)
,university_name varchar(30)
)

drop table if exists #dummytable

create table #dummytable
(
row_id int
,qualification_type varchar(30)
)

insert into #candidate
select 1, 'a',  'Higher Eduation'   ,'University X'      union
select 1, 'a',  'Higher Eduation'   ,'University Y'      union
select 1, 'a',  'Postgrad Education'    ,'University Y'  union
select 1, 'b',  'Higher Eduation'   ,'University U'      union
select 1, 'c',  'Higher Eduation'   ,'University 1'      union
select 1, 'c',  'Postgrad Education'    ,'University 1'  union
select 1, 'd',  'Postgrad Education'    ,'University C'  

insert into #dummytable
select 1, 'Higher Eduation' union
select 1, 'Postgrad Education'

方案一：使用Full Outer Join实现 #

思路：先生成所有候选人与所有学历类型的完整组合，再通过全外连接匹配原表数据，自动补出原表中缺失的学历记录。

-- 生成所有候选人-学历类型的完整组合
WITH all_candidate_qual AS (
    SELECT DISTINCT c.candidate_ref, d.qualification_type
    FROM #candidate c
    CROSS JOIN #dummytable d
)
-- 合并原数据与缺失记录
SELECT 
    COALESCE(c.row_id, 1) AS row_id,
    acq.candidate_ref,
    acq.qualification_type,
    COALESCE(c.university_name, 'Missing') AS university_name
FROM all_candidate_qual acq
FULL OUTER JOIN #candidate c 
    ON acq.candidate_ref = c.candidate_ref 
    AND acq.qualification_type = c.qualification_type
ORDER BY acq.candidate_ref, acq.qualification_type;

方案二：使用Outer Apply实现 #

思路：先获取唯一候选人列表，通过Outer Apply关联所有学历类型，筛选出原表中不存在的学历记录，最后与原数据合并。

-- 合并原数据与缺失记录
SELECT 
    c.row_id,
    c.candidate_ref,
    c.qualification_type,
    c.university_name
FROM #candidate c
UNION ALL
-- 筛选出缺失的学历记录
SELECT 
    1 AS row_id,
    distinct_candidates.candidate_ref,
    dt.qualification_type,
    'Missing' AS university_name
FROM (
    SELECT DISTINCT candidate_ref FROM #candidate
) distinct_candidates
OUTER APPLY #dummytable dt
WHERE NOT EXISTS (
    SELECT 1 
    FROM #candidate c
    WHERE c.candidate_ref = distinct_candidates.candidate_ref 
    AND c.qualification_type = dt.qualification_type
)
ORDER BY candidate_ref, qualification_type;

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