Outer joins and “OR”

If you execute the following:

select * from dual where dummy(+) in (1,2,3);

you get the following error:

ERROR at line 1: ORA-01719: outer join operator (+) not allowed in operand of OR or IN

However, in Oracle 9i and above you can overcome this restriction by using the ANSI SQL syntax. For example:

select ename, dname
from emp left outer join dept on 
    ( emp.deptno = dept.deptno OR emp.empno = 55 );

will not generate the ORA-01719 error.

View the original AskTom discussion about this subject.

Related articles:

• Back to basics: outer joins
• Did You Know This About Outer Joins in Oracle?
• Back to basics: inner joins

Filed in Oracle, Tips on 01 Jul 05 | Tags: join, sql

---