Outer Join Operator (+) Restrictions in 12.1.0.2?

blank

I’m currently reviewing a draft of Roger Troller’s updated PL/SQL and SQL Coding Guidelines version 3.0. One guideline recommends using ANSI join syntax. The mentioned reasons are

ANSI join syntax does not have as many restrictions as the ORACLE join syntax has. Furthermore ANSI join syntax supports the full outer join. A third advantage of the ANSI join syntax is the separation of the join condition from the query filters.

While I read this I wondered which restrictions still exist for ORACLE join syntax nowadays and searched for “(+)” in the current Error Messages documentation (E49325-06) and found the following error messages:

  • ORA-01417: a table may be outer joined to at most one other table
  • ORA-01719: outer join operator (+) not allowed in operand of OR or IN
  • ORA-01799: a column may not be outer-joined to a subquery
  • ORA-25156: old style outer join (+) cannot be used with ANSI joins
  • ORA-30563: outer join operator (+) is not allowed here

In the 9.2 documentation (A96525-01) I found the following additional messages:

  • ORA-01416: two tables cannot be outer-joined to each other
  • ORA-01468: a predicate may reference only one outer-joined table

I’ve written SQL statements to produce the error message listed above on a 9.2.0.8 Oracle database and ran them on a 12.1.0.2 database as well to see which restrictions still exist for the outer join operator (+) as basis for my feedback to Roger. While writing the queries I thought this might be an interesting topic to blog about.

Examples

Example 1: ORA-01416: two tables cannot be outer-joined to each other
SELECT s.*, p.*
  FROM sh.sales s, sh.products p
 WHERE p.prod_id = s.prod_id(+)
       AND p.supplier_id(+) = s.channel_id;

An ORA-01416 is thrown in 9.2.0.8 and 12.1.0.2. You cannot formulate such a query using ANSI join. Doing something like that does not make sense. It is not a relevant restriction. But it is interesting to see that an ORA-01416 is thrown in Oracle 12.1.0.2, even if this error message is not documented any more.

Example 2: ORA-14017: a table may be outer joined to at most one other table
SELECT s.*, c.*, p.*
  FROM sh.sales s, sh.customers c, sh.products p
 WHERE p.prod_id = s.prod_id(+)
       AND c.cust_id = s.cust_id(+);

An ORA-01417 is thrown in 9.2.0.8 but not in 12.1.0.2.

Example 3: ORA-01468: a predicate may reference only one outer-joined table
SELECT s.*, p.*
  FROM sh.sales s, sh.products p
 WHERE p.prod_id(+) = s.prod_id(+);

An ORA-01468 is thrown in 9.2.0.8 and in 12.1.0.2. You cannot formulate such a query using ANSI join. It could have been a way to formulate a full outer join, but something like that is not supported with Oracle join syntax. ORA-01468 is not documented in Oracle 12.1.0.2, but this error is thrown. I do not consider this a relevant restriction for Oracle join syntax.

Example 4: ORA-01719: outer join operator (+) not allowed in operand of OR or IN
SELECT s.*, p.*
  FROM sh.sales s, sh.products p
 WHERE p.prod_id(+) = s.prod_id
   AND p.prod_category(+) IN ('Boys', 'Girls');

An ORA-01719 is thrown in 9.2.0.8 but not in 12.1.0.2.

Example 5a: ORA-01799: a column may not be outer-joined to a subquery
SELECT s.*
  FROM sh.sales s
 WHERE s.time_id(+) = (SELECT MAX(t.time_id)
                         FROM sh.times t);

An ORA-01799 is thrown in 9.2.0.8 and in 12.1.0.2. You cannot formulate such a query using ANSI join. Of course, you may rewrite this to a valid Oracle join or ANSI join query. Here’s an example:

Example 5b: Fix for ORA-01799: a column may not be outer-joined to a subquery
SELECT s.*, t.max_time_id
  FROM sh.sales s,
       (SELECT MAX(t.time_id) AS max_time_id
          FROM sh.times t) t
 WHERE s.time_id(+) = t.max_time_id;

Because the restriction applies to ANSI join as well, I do not consider this a relevant restriction for Oracle join syntax.

Example 6: ORA-25156: old style outer join (+) cannot be used with ANSI joins
SELECT s.*, c.*, p.*
  FROM sh.sales s, sh.customers c
  JOIN sh.products p
    ON (p.prod_id = s.prod_id)
 WHERE c.cust_id = s.cust_id(+);

An ORA-25156 is thrown in 9.2.0.8 and 12.1.0.2. This is not a restriction for Oracle join syntax. The grammar simply does not support mixing join syntax variants.

Example 7: ORA-30563: outer join operator (+) is not allowed here
SELECT lpad(' ', (LEVEL - 1) * 3) || to_char(e.empno) || ' ' || 
       e.ename(+) || 
       ' ' || d.dname AS emp_name
  FROM scott.emp e, scott.dept d
 WHERE e.deptno(+) = d.deptno
CONNECT BY PRIOR e.empno(+) = e.mgr
 START WITH e.ename(+) = 'KING'
 ORDER BY rownum, e.empno(+);

An ORA-30563 is thrown in 9.2.0.8 and 12.1.0.2. Interesting is that if you remove the (+) on the highlighted line 2 the query works on 9.2.0.8 but not on 12.1.0.2. Using the (+) in a CONNECT BY clause, START WITH clause, or ORDER BY clause does not make sense. It is not possible using ANSI-join as well. The important part is the join itself on line 5 and this is working in conjunction with a CONNECT BY. Therefore I do consider this an irrelevant restriction for the Oracle join syntax.

Summary

The results of the example relevant statements are summarized in the following table.

Error message by test SQLRelevant outer join restriction?Result in 9.2.0.8Result in 12.1.0.2
ORA-01416 two tables cannot be outer-joined to each otherNoErrorError
ORA-01417: a table may be outer joined to at most one other tableYesErrorOK
ORA-01468 a predicate may reference only one outer-joined tableNoErrorError
ORA-01719: outer join operator (+) not allowed in operand of OR or INYesErrorOK
ORA-01799: a column may not be outer-joined to a subqueryNoErrorError
ORA-25156: old style outer join (+) cannot be used with ANSI joinsNoErrorError
ORA-30563: outer join operator (+) is not allowed hereNoErrorError

Table 1: Outer join operator (+) restrictions in 9.2.0.8 and 12.1.0.2


In the most current Oracle version, no relevant limitations exist regarding the Oracle join syntax. Hence not choosing ANSI join syntax just because in the past some limitations existed is doing the right for the wrong reasons… I favour the ANSI join syntax because filter and join conditions are separated. For full outer joins, there is simply no better performance option than to use ANSI join syntax. See also also Chris Antognini’s post about native full outer join.

6 Comments

  1. blank Norbert Klasen says:

    In the most current Oracle version …

    Are you talking about 12.1.0.2 as used in your examples or about 12.2 which was released in March 2017?

    Are there any differences regarding Outer Join restrictions between 12.1 and 12.2?

     

    • Hi Norbert,

      At the time of writing no 12.2 was available… However, I just run the queries against an Oracle Database 12.2.0.1.170814. The results are identical to those against version 12.1.0.2.

      HTH
      Philipp

  2. blank Lokesh says:

    Is there any alternate recommendation in Oracle 12c to replace the functionality of using (+) in order by clause

    • Yes. Simply remove the (+) in the order by clause. For example:

      SELECT d.deptno, d.dname, d.loc, e.ename
        FROM dept d, emp e
       WHERE d.deptno = e.deptno (+)
       ORDER BY e.ename NULLS FIRST;
  3. blank soumit says:

    When Try to create the materialized view in oracle 12c system showing error outer join is not allowed here (ora-30563) any solution please help.

    Regards
    soumit

    • The full error message is:

      ORA-30563: outer join operator (+) is not allowed here
      30563. 00000 - "outer join operator (+) is not allowed here"
      *Cause:    An attempt was made to reference (+) in either the select-list,
                 CONNECT BY clause, START WITH clause, or ORDER BY clause.
      *Action:   Do not use the operator in the select-list, CONNECT BY clause,
                 START WITH clause, or ORDER BY clause.

      IMHO the error message is good. I suggest to follow the instructions under “Action”.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.