Oracle join到Ansi join的转换

Question

我需要将以下oracle sql转换为基于ansi的sql-

Select t1.c1, t2.c2, t1.c3 from t1, t2 where
T1.c1=t2.c1(+) and
T1.c2=t2.c2(+) and
T1.c3=t2.c3 and
T1.c4=t2.c4 and
T1.c1='1'

热心的帮助

Answer 1

如果t2中的所有列都具有(+)修饰符，则这将是外连接。

这看起来像是：

Select t1.c1, t2.c2, t1.c3
from t1 left join
     t2 
     on T1.c1 = t2.c1 and T1.c2 = t2.c2 and
        T1.c3 = t2.c3 and T1.c4 = t2.c4
where T1.c1 = '1';

但是，您的版本是一个内连接，因为有些列确实需要匹配--因此在第二个表中需要有一个匹配的行。

因此，真正的等价物是：

Select t1.c1, t2.c2, t1.c3
from t1 join
     t2 
     on T1.c1 = t2.c1 and T1.c2 = t2.c2 and
        T1.c3 = t2.c3 and T1.c4 = t2.c4
where T1.c1 = '1';

而(+)是不相关的。