关于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;