为何RIGHT JOIN后our_sample中无匹配的60条数据被丢弃？

背景信息 #

• 需连接PATSTAT中的两张表：our_sample和tls207_pers_appln
  • our_sample包含4列：appln_id、appln_auth、appln_nr、appln_kind，共2191行数据，其中60行的appln_id在tls207_pers_appln中无匹配
  • tls207_pers_appln包含4列：appln_id、person_id、applt_seq_nr、invt_seq_nr
• 目标：保留our_sample中所有appln_id（即使在tls207_pers_appln中无匹配），因此使用RIGHT JOIN
• 实际结果：生成的视图t2_tot_in_patent仅包含2096个appln_id，丢失了那60条无匹配的数据。预期应为2191-35=2156条（35条因HAVING MAX(invt_seq_nr) > 0被过滤），但实际是2191-60-35=2096条。

用户执行的SQL代码 #

-- compiling total count of inventors per patent: t2_tot_in_patent

DROP VIEW IF EXISTS t2_tot_in_patent;
CREATE VIEW t2_tot_in_patent AS
SELECT m.appln_id, MAX(invt_seq_nr) AS tot_in_patent
FROM patstat2022a.tls207_pers_appln AS t7
RIGHT OUTER JOIN cecilia.our_sample AS m
ON t7.appln_id = m.appln_id 
GROUP BY appln_id
HAVING MAX(invt_seq_nr) > 0

原因分析 #

丢失那60条无匹配数据的核心原因是**HAVING MAX(invt_seq_nr) > 0条件的过滤**：
对于tls207_pers_appln中没有匹配记录的appln_id，invt_seq_nr字段的值为NULL，而MAX(NULL)的计算结果仍然是NULL。在SQL中，NULL与任何数值进行比较（比如> 0）的结果都是不成立的，因此这60条数据直接被HAVING条件排除了。

修正方案 #

方案1：调整HAVING条件，包含NULL的情况 #

直接修改HAVING子句，允许MAX(invt_seq_nr)为NULL或者大于0：

DROP VIEW IF EXISTS t2_tot_in_patent;
CREATE VIEW t2_tot_in_patent AS
SELECT m.appln_id, MAX(invt_seq_nr) AS tot_in_patent
FROM patstat2022a.tls207_pers_appln AS t7
RIGHT OUTER JOIN cecilia.our_sample AS m
ON t7.appln_id = m.appln_id 
GROUP BY appln_id
HAVING MAX(invt_seq_nr) > 0 OR MAX(invt_seq_nr) IS NULL

方案2：用COALESCE处理NULL值 #

如果需要将无匹配的tot_in_patent显示为0（便于统一统计），可以用COALESCE把NULL转为0，再调整HAVING条件：

DROP VIEW IF EXISTS t2_tot_in_patent;
CREATE VIEW t2_tot_in_patent AS
SELECT m.appln_id, COALESCE(MAX(invt_seq_nr), 0) AS tot_in_patent
FROM patstat2022a.tls207_pers_appln AS t7
RIGHT OUTER JOIN cecilia.our_sample AS m
ON t7.appln_id = m.appln_id 
GROUP BY appln_id
HAVING COALESCE(MAX(invt_seq_nr), 0) >= 0

额外建议 #

从语义上看，使用LEFT JOIN会更直观：将our_sample作为左表（主表），这样逻辑上更清晰地表达“保留主表所有数据”的需求，和RIGHT JOIN效果一致，但可读性更强：

DROP VIEW IF EXISTS t2_tot_in_patent;
CREATE VIEW t2_tot_in_patent AS
SELECT m.appln_id, COALESCE(MAX(invt_seq_nr), 0) AS tot_in_patent
FROM cecilia.our_sample AS m
LEFT OUTER JOIN patstat2022a.tls207_pers_appln AS t7
ON m.appln_id = t7.appln_id 
GROUP BY appln_id
HAVING COALESCE(MAX(invt_seq_nr), 0) >= 0

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