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

When you encounter an ORA-01719 error, the following error message will appear:

  • ORA-01719: outer join operator (+) not allowed in operand of OR or IN

You tried to perform an outer join when using an OR condition or IN operator. It is not allowed when using outer joins.

Let’s consider an example below :

as we can see it clearly says that introducing OR condition in outer joins are strictly forbidden. Nobody knows for sure why this strange limitation exists in Oracle version of outer joins, because in ANSI SQL form of outer join we don’t have this limitation and it is works actually, see below

As we can see, ANSI SQL form of LEFT OUTER JOIN worked just fine and did not throw ORA-01719. Therefore this is something you have to remember before rushing to the exam. The only explanation I’ve found for this limitation is unrealistically huge numbers of rows produced by ANSI left outer join. It was 14805 (see below).

It probably explains why Oracle has decided to ban this types of queries, 14805 rows probably do not make any sense, while having just 319 rows in customers table and 105 rows in the orders table.

Having 14805 combinations of c.customer_id and  o.customer_id probably won’t make any sense.

But this situation is very tricky. You have to remember it. In one case it is working, in other case don’t )) This is probably the first case when Oracle syntax of SQL will produce different results if compared to standard ANSI SQL syntax.

 

What Oracle offers is to split WHERE clause to two independent SQL queries and then unite them through UNION ALL. Let’s see how it plays:

As we can see it produces the same 14805 rows. So, no one really knows why Oracle still can’t implement it and still throw ORA 1719 error in their version of outer joins syntax.

 

Leave a comment

Your email address will not be published. Required fields are marked *