关于oracle:oracle在left-join时两表关联的字段为null时失效问题

oracle 在 left join 时两表关联的字段为 null 时生效问题

问题形容

TESTA

TESTB

如果用 left join 会失落掉 TESTB 的空值对应的 120 这个值

SELECT a.*, b.* 
FROM TESTA a
LEFT JOIN TESTB b ON a.subject = b.SUBJECT;

解决方案

在 left join 前要将空数据赋值，

with a as (SELECT nvl(subject, '-')subject, score FROM TESTA ),
b as (SELECT nvl(subject, '-')subject, score FROM TESTB )
SELECT a.*, b.* 
FROM a
LEFT JOIN b ON a.subject = b.SUBJECT;