为何引用外部作用域的嵌套子查询未触发语法错误？

我写了一个多表关联的SQL查询，WHERE子句用了NOT EXISTS嵌套子查询。我原本以为嵌套子查询不能引用FROM子句里没包含的表，但子查询里有两个被注释掉的AND条件——两边都是引用外部查询的表，不是子查询自身的ASSIGNED_EQUIPMENT_ATTRIBUTES表。把这两个条件注释后，查询结果行数没变化，我搞不懂：这两个条件到底有没有实际作用？为什么没触发语法错误？

查询代码 #

FROM 
    reservation 
JOIN 
    ASSIGNED_EQUIPMENT ON ASSIGNED_EQUIPMENT.RESERVATION_ID = reservation.reservation_id
JOIN 
    EQUIPMENT_NODE ON EQUIPMENT_NODE.EQUIPMENT_ID = ASSIGNED_EQUIPMENT.EQUIPMENT_ID
JOIN 
    EQUIPMENT_ATTRIBUTES ON EQUIPMENT_NODE.EQUIPMENT_ID = EQUIPMENT_ATTRIBUTES.EQUIPMENT_ID
                         AND EQUIPMENT_NODE.TYPE_CODE = EQUIPMENT_ATTRIBUTES.EQUIPMENT_TYPE_CODE
JOIN 
    EQUIPMENT_TYPE_ATTRIBUTES ON EQUIPMENT_TYPE_ATTRIBUTES.TYPE_CODE = EQUIPMENT_ATTRIBUTES.EQUIPMENT_TYPE_CODE
                              AND EQUIPMENT_TYPE_ATTRIBUTES.ATTRIBUTE = EQUIPMENT_ATTRIBUTES.ATTRIBUTE
LEFT JOIN 
    SITE AS SITEA ON reservation.origination_site_id = sitea.site_id
LEFT JOIN 
    SITE AS SITEB ON reservation.destination_site_id = siteb.site_id
JOIN 
    SITE AS SITEC ON reservation.origination_office_site = sitec.site_id
JOIN 
    TIMEZONE_ADJUSTMENT AS A ON RESERVATION.ACTUAL_START_TIME >= A.EFFECTIVE_START_DATE 
                             AND RESERVATION.ACTUAL_START_TIME <= A.EFFECTIVE_END_DATE
JOIN 
    TIMEZONE_ADJUSTMENT AS D ON RESERVATION.ACTUAL_END_TIME >= D.EFFECTIVE_START_DATE 
                             AND RESERVATION.ACTUAL_END_TIME <= D.EFFECTIVE_END_DATE
JOIN 
    USER_PROFILE ON A.TIME_ZONE_NAME = USER_PROFILE.USER_TIME_ZONE 
                 AND D.TIME_ZONE_NAME = USER_PROFILE.USER_TIME_ZONE
JOIN 
    CONFIGURATION ON configuration.originating_site = USER_PROFILE.originating_office
WHERE 
    NOT EXISTS (SELECT 1
                FROM ASSIGNED_EQUIPMENT_ATTRIBUTES
                WHERE assigned_equipment_attributes.assigned_equip_id = assigned_equipment.assigned_equip_id
                --AND assigned_equipment.equipment_id = equipment_node.equipment_id
                --AND equipment_node.type_code = equipment_type_attributes.type_code
                  AND assigned_equipment_attributes.attribute = equipment_type_attributes.attribute)

解答 #

1. 为何未触发语法错误？ #

这两个条件属于关联子查询的合法写法：子查询可以引用外部查询中已经加载的表（也就是主查询JOIN过的表），只要这些表在子查询的作用域内，SQL语法就允许这种跨查询的列引用，不会报错。你的误解是把“非关联子查询”的规则套到了关联子查询上——非关联子查询确实不能引用外部表，但关联子查询本身就是靠这种引用实现内外关联的。

2. 这两个条件为何没有实际作用？ #

看主查询的关联逻辑就能明白：

• ASSIGNED_EQUIPMENT和EQUIPMENT_NODE已经通过EQUIPMENT_ID做了JOIN，所以assigned_equipment.equipment_id = equipment_node.equipment_id这个条件在主查询的每一行里都必然成立，放到子查询里重复判断不会过滤掉任何数据。
• 再看EQUIPMENT_NODE到EQUIPMENT_TYPE_ATTRIBUTES的关联链：EQUIPMENT_NODE和EQUIPMENT_ATTRIBUTES通过TYPE_CODE关联，EQUIPMENT_ATTRIBUTES又和EQUIPMENT_TYPE_ATTRIBUTES通过TYPE_CODE关联，这就意味着equipment_node.type_code = equipment_type_attributes.type_code也是恒成立的条件。

简单说，这两个条件是完全冗余的——主查询的JOIN已经把不符合条件的行过滤掉了，子查询里再加一遍不会改变结果。

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