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
执行结果：
