Oracle外连接和常量值 [英] Oracle outer join and constant values

问题描述

======原始问题======

我在SQL上具有多个关于常量的外部联接:

I've multiple outer joins on constants as below in the SQL:

select .. from CLAS MDP, CLAS ORG_CNTRY, CLAS BEN_CNTRY
AND   ORG_CNTRY.LAS_ID(+) = 'COUNTRY_LOOKUP'
AND   ORG_CNTRY.CB_DT     = MDP.CB_DT
AND   BEN_CNTRY.LAS_ID(+) = 'COUNTRY_LOOKUP'
AND   BEN_CNTRY.CB_DT     = MDP.CB_DT

上面的查询是否等同于下面的查询?请告知.

Is the above query equivalent to the below? Please advise.

select ... from
CLAS MDP left join CLAS ORG_CNTRY on ORG_CNTRY.LAS_ID = 'COUNTRY_LOOKUP' left 
join CLAS BEN_CNTRY on BEN_CNTRY.LAS_ID = 'COUNTRY_LOOKUP' where ORG_CNTRY.CB_DT     
= MDP.CB_DT and BEN_CNTRY.CB_DT     = MDP.CB_DT

======后续问题=======

@ Used_By_Already，@ Gordon Linoff感谢您的回复.

@Used_By_Already, @Gordon Linoff thanks for the response.

但是，我的实际查询只不过是我最初发布的内容.就像这样:

However, my actual query is little more than what I originally posted. it goes like:

MDP.CLAS_ID   = 'DIS_PARAMETERS'
AND   MDP.CB_DT    = <<BusinessDate>>
AND   ACCT.ACCT_NO  = TXN.ACCT_NO
AND   MDP.CTRY_CD   = TXN.REC_CTRY_CD
AND   TXN.CB_DT    BETWEEN ACCT.START_DT and ACCT.END_DT
AND   ORG_CNTRY.CLAS_ID(+) = 'COUNTRY_LOOKUP'
AND   ORG_CNTRY.CB_DT     = MDP.CB_DT
AND   BEN_CNTRY.CLAS_ID(+) = 'COUNTRY_LOOKUP'
AND   BEN_CNTRY.CB_DT     = MDP.CB_DT
AND   TXN.SEND_CTRY_CD  = ORG_CNTRY.CLAS_SCHM_CD(+)
AND   TXN.RCIP_CTRY_CD  = BEN_CNTRY.CLAS_SCHM_CD(+)

可以这样重写吗?请告知.

Can this be re-written as below? Please advise.

select ...
from 
CLAS MDP left join CLAS ORG_CNTRY on ORG_CNTRY.CB_DT = MDP.CB_DT 
    AND ORG_CNTRY.LAS_ID = 'COUNTRY_LOOKUP' 
right join ACCT_TRAN TXN on ORG_CNTRY.CLAS_SCHM_CD = TXN.SEND_CTRY_CD
left join CLAS BEN_CNTRY on BEN_CNTRY.CB_DT = MDP.CB_DT 
    AND BEN_CNTRY.LAS_ID = 'COUNTRY_LOOKUP' 
right join ACCT_TRAN TXN on BEN_CNTRY.CLAS_SCHM_CD = TXN.RCIP_CTRY_CD

推荐答案

每个谓词都提到2个表，这些表使它们成为"联接条件"(而不是过滤条件").

Each of these predicates mentions 2 tables which makes them "join conditions" (not "filtering conditions").

    AND   ORG_CNTRY.CB_DT = MDP.CB_DT
    AND   BEN_CNTRY.CB_DT = MDP.CB_DT

如果从老式" Oracle连接语法进行转换，则将那些"连接条件" 必须移至连接语法，如下所示:

If converting from the "old fashioned" Oracle join syntax then those "join conditions" MUST be moved to the join syntax, like this:

select ...
from CLAS MDP 
left join CLAS ORG_CNTRY on ORG_CNTRY.CB_DT = MDP.CB_DT
left join CLAS BEN_CNTRY on BEN_CNTRY.CB_DT = MDP.CB_DT

希望现在可以清楚地发现，其他谓词(在常量上)在where子句中没有任何意义，否则外部联接将被覆盖为等同于内部联接.因此，这些也需要移到联接中，您最终会得到:

Hopefully it now becomes clear that the other predicates (on the constants) would not make sense in the where clause or the outer joins would be overridden to be equivalent to inner joins. So these also need to be moved to the joins and you end up with:

select ...
from CLAS MDP 
left join CLAS ORG_CNTRY on ORG_CNTRY.CB_DT = MDP.CB_DT AND ORG_CNTRY.LAS_ID = 'COUNTRY_LOOKUP' 
left join CLAS BEN_CNTRY on BEN_CNTRY.CB_DT = MDP.CB_DT AND BEN_CNTRY.LAS_ID = 'COUNTRY_LOOKUP'

---

尽管以下注释绝不是强制性的，但我建议先在较新的连接语法中列出优先级表"(注意 MDP 被列为以下任一类型的 prior 其他表格):

---

Whilst the following note is by no means mandatory, I recommend listing the "prior table" first in the newer join syntax (note MDP is listed prior to either of the other tables):

select ...
from CLAS MDP
left join CLAS ORG_CNTRY on MDP.CB_DT = ORG_CNTRY.CB_DT AND ORG_CNTRY.LAS_ID = 'COUNTRY_LOOKUP' 
left join CLAS BEN_CNTRY on MDP.CB_DT = BEN_CNTRY.CB_DT AND BEN_CNTRY.LAS_ID = 'COUNTRY_LOOKUP'

从较旧的(非常混乱)where子句语法转换时，可以忽略的一件事是FROM列表中的顺序列表没有关系.但是，当使用诸如 join t2 ON t1.x = t2.y 之类的语法时，必须在t2之前列出表t1，否则联接将产生错误.我发现首先列出优先级表"有助于确保这一点是正确的.

One thing that can be overlooked when converting from the older (very messy) where clause syntax is that order listing tables in the FROM list did not matter. However when using syntax such as join t2 ON t1.x = t2.y the table t1 MUST be listed before t2 is or the join will produce an error. I find that listing the "prior table" first helps ensure this is true.

顺便说一句:在原始语法"中，您将需要以下内容来正确形成这两个外部联接:

By the way: In the "archaic syntax" you would need the following to properly form those two outer joins:

select .. from CLAS MDP, CLAS ORG_CNTRY, CLAS BEN_CNTRY
AND   ORG_CNTRY.LAS_ID(+) = 'COUNTRY_LOOKUP'
AND   ORG_CNTRY.CB_DT(+)     = MDP.CB_DT
AND   BEN_CNTRY.LAS_ID(+) = 'COUNTRY_LOOKUP'
AND   BEN_CNTRY.CB_DT(+)     = MDP.CB_DT

否则，通过要求一个表中的列值等于另一个表中某个值的字段来形成内部联接(因此，不允许使用不匹配的值).

Otherwise you have formed inner joins by requiring a column value in one table to be equal to a field in some value in another table (& hence unmatched values are disallowed).

其他查询(更多联接)

一旦建立了LEFT JOIN，并且想要将另一个表与该左联接表相关联，请继续使用LEFT JOIN.另外请注意，每个表别名都必须是唯一的，因此，如果您需要多次连接任何表，请确保使用的别名不相同.

Once you have established a LEFT JOIN, and you want to relate another table to that left joined table continue to use LEFT JOIN. Also note that every table alias MUST BE UNIQUE, so if you need to join any table more than once make sure the alias used isn't the same.

SELECT
      *
FROM CLAS MDP
LEFT JOIN CLAS ORG_CNTRY ON MDP.CB_DT = ORG_CNTRY.CB_DT AND ORG_CNTRY.LAS_ID = 'COUNTRY_LOOKUP'
LEFT JOIN CLAS BEN_CNTRY ON MDP.CB_DT = BEN_CNTRY.CB_DT AND BEN_CNTRY.LAS_ID = 'COUNTRY_LOOKUP'
LEFT JOIN ACCT_TRAN TXN1 ON ORG_CNTRY.CLAS_SCHM_CD = TXN1.SEND_CTRY_CD
LEFT JOIN ACCT_TRAN TXN2 ON BEN_CNTRY.CLAS_SCHM_CD = TXN2.RCIP_CTRY_CD

这篇关于Oracle外连接和常量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！