内连接子查询匹配行无法返回，咨询解决方案及左外连接适用性

核心问题原因 #

你当前的SQL里，子查询的ROWNUM = 1是在整个子查询结果集里取任意第一条记录，而不是针对每个商品ID（ITEM_ID）取第一条匹配的UOM数据。这就导致子查询返回的大概率是其他商品的记录，和主表的11223匹配不上，所以内连接查不到结果；换成左外连接时，主表数据会强制返回，但子查询匹配不上的话UNIT_OF_MEASURE会是null，你某次能得到预期数据只是巧合（刚好子查询取到了目标商品的记录），逻辑完全不可靠。

解决方法 #

方法1：用关联子查询（支持引用外部表字段）

Oracle 12c及以上版本可以用LATERAL关键字，让子查询直接引用主表的ITM.INVENTORY_ITEM_ID，实现针对每个主表商品取第一条匹配的UOM：

SELECT ITM.INVENTORY_ITEM_ID, ORG.ORGANIZATION_CODE, PURCHASE_UOM_CODE.UNIT_OF_MEASURE
FROM EGP_SYSTEM_ITEMS ITM 
INNER JOIN LATERAL (
    SELECT UOMT.UNIT_OF_MEASURE, POL.ITEM_ID
    FROM INV_UNITS_OF_MEASURE_TL UOMT
    JOIN INV_UNITS_OF_MEASURE_B UOMB ON UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID
    JOIN PO_LINES_ALL POL ON UOMB.UOM_CODE = POL.UOM_CODE
    JOIN PO_HEADERS_ALL POH ON POH.PO_HEADER_ID = POL.PO_HEADER_ID
    WHERE POL.ITEM_ID = ITM.INVENTORY_ITEM_ID
      AND UOMT.LANGUAGE = USERENV('LANG')                            
      AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
    FETCH FIRST 1 ROWS ONLY
) PURCHASE_UOM_CODE ON PURCHASE_UOM_CODE.ITEM_ID = ITM.INVENTORY_ITEM_ID
WHERE ITM.INVENTORY_ITEM_ID = '11223'

方法2：用窗口函数分组取第一条记录

通过ROW_NUMBER()按商品ID分组，给每组记录编号，取编号为1的第一条（可通过ORDER BY指定排序规则，比如取最新的PO单）：

SELECT ITM.INVENTORY_ITEM_ID, ORG.ORGANIZATION_CODE, PURCHASE_UOM_CODE.UNIT_OF_MEASURE
FROM EGP_SYSTEM_ITEMS ITM 
INNER JOIN (
    SELECT 
        UOMT.UNIT_OF_MEASURE, 
        POL.ITEM_ID,
        ROW_NUMBER() OVER (PARTITION BY POL.ITEM_ID ORDER BY POH.CREATION_DATE DESC) AS rn
    FROM INV_UNITS_OF_MEASURE_TL UOMT
    JOIN INV_UNITS_OF_MEASURE_B UOMB ON UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID
    JOIN PO_LINES_ALL POL ON UOMB.UOM_CODE = POL.UOM_CODE
    JOIN PO_HEADERS_ALL POH ON POH.PO_HEADER_ID = POL.PO_HEADER_ID
    WHERE UOMT.LANGUAGE = USERENV('LANG')                            
      AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
) PURCHASE_UOM_CODE ON PURCHASE_UOM_CODE.ITEM_ID = ITM.INVENTORY_ITEM_ID
WHERE ITM.INVENTORY_ITEM_ID = '11223'
  AND PURCHASE_UOM_CODE.rn = 1

关于LEFT OUTER JOIN的选择 #

• 如果业务需求是即使商品没有对应的BLANKET PO记录，也要返回商品基础信息，就用LEFT OUTER JOIN，但必须先修复子查询逻辑（确保是按商品分组取第一条，而非全局取一条）；
• 如果只需要返回有对应BLANKET PO的商品，就用INNER JOIN，配合上面两种方法即可得到准确结果。

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