Tag Archives: ANSI SQL

ORA-01719 is partially relaxed

You most likely have seen this error before:

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)).

In 11gR2 it is no longer raised for predicates like column(+) IN (constant1, constant2, …).

Continue reading

Native full outer join

Starting with version 11gR1 Oracle database uses native FULL JOIN implementation based on HASH JOIN whenever possible. It’s good to know that this functionality first appeared in Oracle and could be used to overcome different issues, including performance and bugs. Here is an example appeared on the SQL.ru recently:

Continue reading