时间戳

首页 > 解决方案 > Oracle SQL 内连接不返回具有空值的记录

sql - Oracle SQL 内连接不返回具有空值的记录

问题描述

我将两个表连接在一起，但结果集不包含空白行。下面的查询不返回具有空值的列。这是为什么？

SELECT
  table1.FE_KEY
 ,table2.CV_VALUE
 ,table2.CV_UOM AS EST_ISENTROPIC_POWERUoM
 ,CVDATA1.CV_VALUE
 ,CVDATA1.CV_UOM AS VAPOUR_OR_GAS_HANDLEUoM
 ,CVDATA2.CV_VALUE
 ,CVDATA2.CV_UOM AS NORMAL_FLOW_RATEUoM     
FROM
  ((table1 FULL LEFT JOIN table2 ON table1.ID = table2.FE_ID)
  INNER JOIN table2 CVDATA1 ON table1.ID = CVDATA1.FE_ID)
  INNER JOIN table2 CVDATA2 ON table1.ID = CVDATA2.FE_ID    
WHERE ((table1.FE_KEY) Like '6-K-%')
  AND ((table2.CV_CODE)='EST_ISENTROPIC_POWER')
  AND ((CVDATA1.CV_CODE)='VAPOUR_OR_GAS_HANDLE')
  AND ((CVDATA2.CV_CODE)='NORMAL_FLOW_RATE')

标签： sqloracle

解决方案

如果要包含 FE_KEY 或 CV_CODE 为空的行，则需要显式包含这些行。您似乎还有一些不需要的额外括号。试试这个重新设计的版本：

SELECT
  TABLE1.FE_KEY
 ,TABLE2.CV_VALUE
 ,TABLE2.CV_UOM AS EST_ISENTROPIC_POWERUOM
 ,CVDATA1.CV_VALUE
 ,CVDATA1.CV_UOM AS VAPOUR_OR_GAS_HANDLEUOM
 ,CVDATA2.CV_VALUE
 ,CVDATA2.CV_UOM AS NORMAL_FLOW_RATEUOM
FROM
  (
   (TABLE1 FULL LEFT JOIN TABLE2 ON TABLE1.ID = TABLE2.FE_ID)
   INNER JOIN TABLE2 CVDATA1 ON TABLE1.ID = CVDATA1.FE_ID
  )
  INNER JOIN TABLE2 CVDATA2 ON TABLE1.ID = CVDATA2.FE_ID
WHERE (TABLE1.FE_KEY IS NULL OR TABLE1.FE_KEY LIKE '6-K-%')
  AND (TABLE2.CV_CODE IS NULL OR TABLE2.CV_CODE = 'EST_ISENTROPIC_POWER')
  AND (CVDATA1.CV_CODE IS NULL OR CVDATA1.CV_CODE = 'VAPOUR_OR_GAS_HANDLE')
  AND (CVDATA2.CV_CODE IS NULL OR CVDATA2.CV_CODE = 'NORMAL_FLOW_RATE')

推荐阅读