将传统外连接转换为 ANSI [英] Conversion of legacy outer join to ANSI

问题描述

我遇到了以下遗留 PL/SQL，发现针对标量常量的外连接有些令人困惑.首先，有人可以确认我将其转换为 ANSI 的尝试是正确的.

I have come across the following legacy PL/SQL and find the outer joins against scalar constants somewhat confusing. First of all, can someone please confirm that my attempt to convert this to ANSI is correct.

旧代码:

cursor c1item (c1item_iel_id number) is  
select  
  `<columns>`  
from  iel_item iit, iel_item_property iip  
  where iit.iit_change_type = 'I'  
  and   iip.iip_change_type (+) = 'I'  
  and   iip.it_id (+)  = iit.it_id  
  and   iit.iel_id = c1item_iel_id  
  and   iip.iel_id (+) = c1item_iel_id;

ANSI 代码

cursor c1item (c1item_iel_id number) is  
select  
   `<columns>`  
from  iel_item iit  
left outer join iel_item_property iip  
on  iip.it_id = iit.it_id  
    and  iit.iit_change_type = 'I'  
    and  iip.iip_change_type = 'I'  
    and  iit.iel_id = c1item_iel_id  
    and  iip.iel_id = c1item_iel_id;

如果这是正确的，那么我看不到使用外连接的意义.当然，如果表 iit 中的主键 it_id 在表 iip 中没有对应的外键，那么 iip.iit_change_type 和 iip.iel_id 将为 NULL，在这种情况下，它们将被 AND 子句过滤掉.那么为什么不直接使用内部连接呢?我错过了什么吗?还是原来的代码是胡说八道?

If this is correct, then I don't see the point of using an outer join. Surely if the primary key it_id in table iit does not have a corresponding foreign key in table iip then both iip.iit_change_type and iip.iel_id will be NULL, in which case they will be filtered out by the AND clauses. So why not just use an inner join? Am I missing something? or is the original code nonsense?

推荐答案

不，这是不正确的——只有那些标有(+)"的需要在 LEFT JOIN 中，其余的用于 WHERE 子句:

No, it's not correct -- only those marked with the "(+)" need to be in the LEFT JOIN, the rest are for the WHERE clause:

   SELECT `<columns>`  
     FROM iel_item iit  
LEFT JOIN iel_item_property iip  ON iip.it_id = iit.it_id  
                                AND iip.iip_change_type = 'I'  
                                AND iip.iel_id = c1item_iel_id
    WHERE iit.iit_change_type = 'I'      
      AND iit.iel_id = c1item_iel_id  

位置对 OUTER JOIN 很重要——ON 子句中的条件在 JOIN 之前应用，而 WHERE 中的条件在 JOIN 之后应用.这会极大地影响返回的结果集，这取决于数据和设置.INNER JOINS 的位置无关紧要 - 在 WHERE 或 ON 子句中，结果集将相同.

Placement matters with OUTER JOINs -- criteria in the ON clause is applied before the JOIN, while criteria in the WHERE is applied after the JOIN. This can greatly affect the result set returned, depends on data and setup. Placement doesn't matter for INNER JOINS - in the WHERE or ON clause, the result set will be the same.

这篇关于将传统外连接转换为 ANSI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！