嵌套子查询SUB2单独运行有值，主查询中返回全NULL问题求助

我尝试使用SQL嵌套查询重现Access数据库报表，已完成脚本转换，但未得到预期结果。SUB1查询返回符合预期的结果，但主查询中SUB2的所有字段均为NULL，恳请帮忙排查问题：

SELECT  SUB1.CUSNAME, SUB1.CO_INSTALL AS Install, SUB1.SALESREPID AS [Sales Rep], SUB1.CUSORDID AS [Job #], SUB1.LINE_PARTID, SUB2.POID AS PO, 
        SUB2.ORDER_DATE AS Ord, SUB2.PROMISE_DATE AS Req, SUB2.LAST_RECEIVED_DATE AS Rcd, SUB2.SHIPTO_NAME AS [Where Sent], SUB2.VendorName AS Vendor
FROM            
(SELECT DISTINCT CUST_ORDER_LINE.CUST_ORDER_ID AS CUSORDID, CUSTOMER_ORDER.DESIRED_SHIP_DATE AS CO_INSTALL, CUSTOMER.NAME AS CUSNAME, CUSTOMER_ORDER.SALESREP_ID AS SALESREPID, 
               CUSTOMER_ORDER.ORDER_TYPE AS CUSORDTYPE, CUSTOMER_ORDER.STATUS, CUST_ORDER_LINE.PART_ID AS LINE_PARTID, CUST_ORDER_LINE.ORDER_QTY, CUST_ORDER_LINE.LINE_STATUS, 
               CUST_ORDER_LINE.DESIRED_SHIP_DATE AS LINE_INSTALL, DEMAND_SUPPLY_LINK.RECEIVED_QTY, DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID AS SUB1BASE, DEMAND_SUPPLY_LINK.SUPPLY_LOT_ID, 
               DEMAND_SUPPLY_LINK.SUPPLY_SPLIT_ID, DEMAND_SUPPLY_LINK.SUPPLY_SEQ_NO, CUSTOMER_ORDER.USER_4, CUSTOMER_ORDER.USER_5
               FROM CUSTOMER_ORDER INNER JOIN
                    CUSTOMER ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.ID INNER JOIN
                        DEMAND_SUPPLY_LINK RIGHT OUTER JOIN
                            CUST_ORDER_LINE ON DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO = CUST_ORDER_LINE.LINE_NO AND DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUST_ORDER_LINE.CUST_ORDER_ID 
                            ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID
                WHERE  (CUSTOMER_ORDER.STATUS <> 'C') AND (CUSTOMER_ORDER.STATUS <> 'X') AND (DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID IS NOT NULL)) 
                AS SUB1 
FULL OUTER JOIN
(SELECT DISTINCT WORK_ORDER.BASE_ID AS WO_BASE, DEMAND_SUPPLY_LINK_1.DEMAND_NO, DEMAND_SUPPLY_LINK_1.DEMAND_SEQ_NO, DEMAND_SUPPLY_LINK_1.DEMAND_TYPE, 
               WORK_ORDER.STATUS AS WO_STAT, DEMAND_SUPPLY_LINK_1.SUPPLY_TYPE, DEMAND_SUPPLY_LINK_1.SUPPLY_BASE_ID AS SUB2BASE, PURCHASE_ORDER.ID AS POID, PURCHASE_ORDER.VENDOR_ID, 
               VENDOR.NAME AS VendorName, PURCHASE_ORDER.ORDER_DATE, PURCHASE_ORDER.DESIRED_RECV_DATE AS DESRECDATE, PURCHASE_ORDER.PROMISE_DATE, 
               PURCHASE_ORDER.LAST_RECEIVED_DATE, SHIPTO_ADDRESS.NAME AS SHIPTO_NAME, PURCHASE_ORDER.STATUS AS PO_STAT, PURCHASE_ORDER.BUYER
               FROM WORK_ORDER INNER JOIN
                        DEMAND_SUPPLY_LINK AS DEMAND_SUPPLY_LINK_1 ON WORK_ORDER.SUB_ID = DEMAND_SUPPLY_LINK_1.DEMAND_SUB_ID AND 
                        WORK_ORDER.SPLIT_ID = DEMAND_SUPPLY_LINK_1.DEMAND_SPLIT_ID AND WORK_ORDER.LOT_ID = DEMAND_SUPPLY_LINK_1.DEMAND_LOT_ID AND 
                        WORK_ORDER.BASE_ID = DEMAND_SUPPLY_LINK_1.DEMAND_BASE_ID INNER JOIN
                        PURCHASE_ORDER LEFT OUTER JOIN
                            SHIPTO_ADDRESS ON PURCHASE_ORDER.SHIPTO_ADDR_NO = SHIPTO_ADDRESS.ADDR_NO ON DEMAND_SUPPLY_LINK_1.SUPPLY_BASE_ID = PURCHASE_ORDER.ID INNER JOIN
                                VENDOR ON PURCHASE_ORDER.VENDOR_ID = VENDOR.ID
) 
AS SUB2 
ON SUB1.SUB1BASE = SUB2.SUB2BASE
WHERE (SUB1.CO_INSTALL IS NOT NULL) AND (UPPER(SUB1.CUSORDID) LIKE 'E%' OR UPPER(SUB1.CUSORDID) like 'D%') 
ORDER BY Install

排查关键点及解决方案 #

1. 验证SUB2自身是否返回有效数据 #

单独执行SUB2的子查询，检查是否有非空的POID、SUB2BASE等字段。如果SUB2本身无结果，说明其内部表连接逻辑存在问题：

• 检查WORK_ORDER与DEMAND_SUPPLY_LINK_1的连接条件是否完整，是否遗漏了必要的关联字段；
• 检查PURCHASE_ORDER与DEMAND_SUPPLY_LINK_1的连接是否正确，是否因INNER JOIN过滤掉了本该匹配的数据（可临时改为LEFT JOIN测试）。

2. 验证关联字段的匹配性 #

主查询通过SUB1.SUB1BASE = SUB2.SUB2BASE关联，需确认两者的匹配情况：

• 执行以下查询，查看SUB1中的SUB1BASE是否在SUB2的SUB2BASE中存在：

SELECT DISTINCT SUB1.SUB1BASE
FROM (
    -- 此处复制SUB1的完整查询语句
    SELECT DISTINCT CUST_ORDER_LINE.CUST_ORDER_ID AS CUSORDID, ... /* SUB1完整内容 */
) AS SUB1
WHERE SUB1.SUB1BASE NOT IN (
    SELECT DISTINCT SUB2BASE FROM (
        -- 此处复制SUB2的完整查询语句
        SELECT DISTINCT WORK_ORDER.BASE_ID AS WO_BASE, ... /* SUB2完整内容 */
    ) AS SUB2
)

如果查询返回结果，说明SUB1的SUB1BASE在SUB2中无对应值，导致关联失败。

3. 检查关联字段的数据类型 #

确认SUB1.SUB1BASE和SUB2.SUB2BASE的数据类型是否一致（例如都是VARCHAR(50)或INT）。数据类型不匹配会导致隐式转换，进而无法正确关联。

4. 分析JOIN类型与WHERE条件的冲突 #

主查询使用FULL OUTER JOIN，但WHERE子句中添加了SUB1.CO_INSTALL IS NOT NULL，这会将查询效果转为类似LEFT JOIN（只保留SUB1有数据的行）。如果SUB2的关联字段与SUB1完全不匹配，自然会返回NULL。

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