如何将NULL值显示为0，并以building为公共字段关联多表？

你的查询核心问题出在带双引号的字段别名与无引号的字段引用不匹配，导致左连接关联失效，进而使NVL无法正确将NULL转为0。以下是具体修正方案：

推荐修正写法（去掉双引号，避免大小写歧义） #

SELECT l.building AS BUILD, NVL(s.numsections, 0) AS NUMSECTIONS
FROM location l
LEFT OUTER JOIN 
    (SELECT l.building, COUNT(*) AS numsections
     FROM location l
     INNER JOIN sections e ON l.locationid = e.locationid
     INNER JOIN courses c ON e.courseid = c.courseid
     WHERE c.subjectcode IN ('CS', 'NET', 'WEB', 'MATH', 'ENGL', 'HIST', 'ZOOL', 'ART', 'COMM', 'BSAD')
     GROUP BY l.building)s
ON l.building = s.building

修正点说明 #

1. 移除所有带双引号的别名：多数数据库（如Oracle）对带双引号的标识符会严格区分大小写，无引号的字段引用会自动转为大写，两者不匹配会导致关联失败或字段找不到。
2. 明确引用子查询字段：主查询中用s.numsections替代NUMSECTIONS，确保NVL能正确作用于统计结果。
3. 子查询中显式指定l.building：避免字段来源歧义，提升可读性。

保留双引号的修正写法（若业务要求必须使用） #

SELECT l.building AS "BUILD", NVL(s."NUMSECTIONS", 0) AS "NUMSECTIONS"
FROM location l
LEFT OUTER JOIN 
    (SELECT l.building AS "BUILD", COUNT(*) AS "NUMSECTIONS"
     FROM location l
     INNER JOIN sections e ON l.locationid = e.locationid
     INNER JOIN courses c ON e.courseid = c.courseid
     WHERE c.subjectcode IN ('CS', 'NET', 'WEB', 'MATH', 'ENGL', 'HIST', 'ZOOL', 'ART', 'COMM', 'BSAD')
     GROUP BY l.building)s
ON l.building = s."BUILD"

修正点说明 #

关联条件和NVL函数中，严格使用带双引号的字段名s."BUILD"和s."NUMSECTIONS"，与子查询的别名保持完全一致。

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