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

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

问题形容

TESTA

TESTB

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

SELECT a.*, b.* FROM TESTA aLEFT 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 aLEFT JOIN b ON a.subject = b.SUBJECT;