Oracle外连接+运算符改写验证：数百条SQL转换是否正确？

原SQL代码 #

SELECT  DISTINCT 
:s1 || '^' || ll.street || '^' || tat.attribute_name    
FROM    
tsec ,
    ll,
    ta,
    tat
WHERE   
    tat.reference       LIKE 'ENGPYMNT%'
    AND tat.system_name     = 'LAND'
    AND ta.attribute_type_id        = tat.attribute_type_id 
    AND ll.legal_id         = ta.source_id
    AND tsec.program(+)     = 'TD_ATTRIBUTE_DETAILS'
    AND tsec.item_name(+)       = 'ATTRIBUTES VIEW'
    AND tsec.system_name(+)     = 'LAND'
    AND tsec.sql_user(+)        = :SqlUser
    AND tsec.relation_type(+)       = 'ATTRIBUTE'
    AND tsec.relation_id(+)     = tat.attribute_type_id
    AND tsec.sec_level      > 0
    AND LTRIM(TO_CHAR(ll.house, '999999'))  LIKE :s1
    AND ll.street           LIKE UPPER( :s2 )
    AND tat.attribute_name      LIKE :s3;

改写后SQL代码 #

SELECT DISTINCT
    :s1 || '^' || land_legal.street || '^' || td_attribute_types.attribute_name 
FROM
    ta JOIN tat ON ta.attribute_type_id = tat.attribute_type_id 
JOIN 
    ll ON ll.legal_id = ta.source_id
LEFT JOIN 
    tsec ON tsec.relation_id = tat.attribute_type_id
    AND tsec.program = 'TD_ATTRIBUTE_DETAILS'
    AND tsec.item_name = 'ATTRIBUTES VIEW'
    AND tsec.system_name = 'LAND'
    AND tsec.sql_user = :SqlUser
    AND tsec.relation_type = 'ATTRIBUTE'
WHERE
    tsec.sec_level > 0
    AND tat.reference LIKE 'ENGPYMNT%'
    AND LTRIM(TO_CHAR(ll.house, '999999')) LIKE :s1
    AND ll.street LIKE UPPER( :s2 )
    AND tat.attribute_name LIKE :s3;

存在的错误分析 #

• 表别名未定义：改写后的SQL中使用land_legal和td_attribute_types引用表列，但FROM子句并未将原表ll、tat对应别名，直接触发语法错误。
• 遗漏核心过滤条件：原SQL中tat.system_name = 'LAND'是tat表的关键过滤规则，改写后WHERE子句完全遗漏该条件，会返回不符合该规则的记录，与原逻辑不符。
• 外连接逻辑的隐性说明：原SQL通过(+)实现外连接，结合WHERE中无(+)修饰的tsec.sec_level > 0，实际效果等同于内连接——当tsec无匹配记录时，tsec.sec_level为NULL，NULL > 0不成立，主表对应记录会被过滤。改写后用LEFT JOIN仍保留该条件在WHERE中，逻辑与原SQL一致；若原意图是保留主表所有符合条件的记录（即使tsec无匹配），则两处SQL都存在逻辑错误，需将tsec.sec_level > 0移至LEFT JOIN的ON子句中。

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