解决：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

执行结果：