解决：Oracle数据库中Left join on 后面为null时匹配不上-阿里云开发者社区

解决：Oracle数据库中Left join on 后面为null时匹配不上

2023-08-10 58

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 解决：Oracle数据库中Left join on 后面为null时匹配不上

+关注继续查看

1：记录

最近在公司开发遇到一个小需求，在级联查询时，当ON.... AND .... 时，ON后面的条件为NULL ，AND 后面的条件不为NULL。要求返回A表与B表匹配上的数据。

2:遇到问题

当ON 后面的条件为NULL，AND 后的条件不为NULL。

例：

ON NULL = NULL AND 6 = 6

使用级联查询时，只返回主表中的数据，次表中的数据却没有匹配上显示都是NULL(可是明明都有数据)

3：解决过程

3.1 临时表A

SQL：

SELECT 
            PROD_TYPE,
            CAST(COUNT(WORK_ORDER_ID) AS float) AS 计划数量,
            SUBSTR(START_DATE, 1, 7)            AS START_DATE,
 
            MAX(TO_CHAR(DATA_TIME, 'YYYY-MM'))  AS 统计日期,
            MAX(STAT_TIME)                      AS 创建时间
     FROM DWS_WORK_ORDER_INFO
     GROUP BY SUBSTR(START_DATE, 1, 7), PROD_TYPE

3.2 临时表B

SQL：

 SELECT CAST(COUNT(WORK_ORDER_ID) AS float) AS 完成数量,
           SUBSTR(START_DATE,1,7)      AS START_DATE,
           PROD_TYPE
    FROM BRAIN.DWS_WORK_ORDER_INFO
    WHERE IF_COMP = '是'
    GROUP BY SUBSTR(START_DATE,1,7), PROD_TYPE

此时我想通过LEFT JOIN 左关联查出 PROD_TYPE = NULL 时的计划数量和完成数量关联条件为：

ON a.PROD_TYPE = b.PROD_TYPE
AND a.START_DATE = b.START_DATE

3.3 错误SQL（不符合需求）

 
select
    a.计划数量,
    a.PROD_TYPE,
    b.PROD_TYPE,
    a.START_DATE,
    b.START_DATE,
    b.完成数量
 
from(SELECT 
            PROD_TYPE,
            CAST(COUNT(WORK_ORDER_ID) AS float) AS 计划数量,
            SUBSTR(START_DATE, 1, 7)            AS START_DATE,
 
            MAX(TO_CHAR(DATA_TIME, 'YYYY-MM'))  AS 统计日期,
            MAX(STAT_TIME)                      AS 创建时间
     FROM DWS_WORK_ORDER_INFO
     GROUP BY SUBSTR(START_DATE, 1, 7), PROD_TYPE) a
         LEFT JOIN (
    --已完成
    SELECT CAST(COUNT(WORK_ORDER_ID) AS float) AS 完成数量,
           SUBSTR(START_DATE,1,7)      AS START_DATE,
           PROD_TYPE
    FROM DWS_WORK_ORDER_INFO
    WHERE IF_COMP = '是'
    GROUP BY SUBSTR(START_DATE,1,7), PROD_TYPE ) b
             ON  a.PROD_TYPE = b.PROD_TYPE
            AND a.START_DATE = b.START_DATE

执行后的结果：

很明显2023-10月的 PROD_TYPE为null 时临时表B的数据没有匹配上，后来查询资料发现，联表查询时，null≠null

4：解决方法

这里可以使用 NVL() ,IFNULL(), 函数均可以
 
COALESCE(a.PROD_TYPE,-1) = COALESCE(b.PROD_TYPE,-1)

 
select
    a.计划数量,
    a.PROD_TYPE,
    b.PROD_TYPE,
    a.START_DATE,
    b.START_DATE,
    b.完成数量
 
from(SELECT 
            PROD_TYPE,
            CAST(COUNT(WORK_ORDER_ID) AS float) AS 计划数量,
            SUBSTR(START_DATE, 1, 7)            AS START_DATE,
 
            MAX(TO_CHAR(DATA_TIME, 'YYYY-MM'))  AS 统计日期,
            MAX(STAT_TIME)                      AS 创建时间
     FROM DWS_WORK_ORDER_INFO
     GROUP BY SUBSTR(START_DATE, 1, 7), PROD_TYPE) a
         LEFT JOIN (
    --已完成
    SELECT CAST(COUNT(WORK_ORDER_ID) AS float) AS 完成数量,
           SUBSTR(START_DATE,1,7)      AS START_DATE,
           PROD_TYPE
    FROM DWS_WORK_ORDER_INFO
    WHERE IF_COMP = '是'
    GROUP BY SUBSTR(START_DATE,1,7), PROD_TYPE ) b
             -- 这里可以使用 NVL() ,IFNULL() 函数均可以
             ON  COALESCE(a.PROD_TYPE,-1) = COALESCE(b.PROD_TYPE,-1)
            AND a.START_DATE = b.START_DATE

执行结果：