讨论ORA-01719 错误的可靠性

讨论ORA-01719 错误的可靠性
ORA-01719 outer join operator (+) not allowed in operand of OR or IN

Cause: An outer join appears in an or clause.

Action: If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select where (B)).

oracle不允许(+)操作符与or和in 操作符一起使用．


SQL> select a.dname,b.ename from dept a,emp b where a.deptno=b.deptno(+) and (b.deptno(+)=10 OR B.DEPTNO(+)=20);

ERROR 位于第 1 行:
ORA-01719: OR 或 IN 操作数中不允许外部连接运算符 (+)


SQL> select a.dname,b.ename from dept a,emp b where a.deptno=b.deptno(+) and (b.deptno(+) 2 in(10,20));

ERROR 位于第 2 行:
ORA-01719: OR 或 IN 操作数中不允许外部连接运算符 (+)

目前为止，oracle报的错误很正常，那看下面这句：


SQL> select a.dname,b.ename from dept a,emp b where a.deptno=b.deptno(+) and (b.deptno(+) in (10) );


DNAME ENAME
-------------- ----------
ACCOUNTING CLARK
ACCOUNTING KING
ACCOUNTING MILLER
RESEARCH
SALES
OPERATIONS

已选择6行。


oralce没有报错，难道in 包含1条记录的时候默认为＝操作符?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8382469/viewspace-259863/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8382469/viewspace-259863/