Oracle (Old) Joins - a Tool/Script for Conversion

Oracle (Old?) Joins - A tool/script for conversion?

The (+) is Oracle specific pre-ANSI-92 OUTER JOIN syntax, because ANSI-89 syntax doesn't provide syntax for OUTER JOIN support.

Whether it is RIGHT or LEFT is determined by which table & column reference the notation is attached to. If it is specified next to a column associated with the first table in the FROM clause - it's a RIGHT join. Otherwise, it's a LEFT join. This a good reference for anyone needing to know the difference between JOINs.

First query re-written using ANSI-92 syntax:

    SELECT e.lastname,
           d.department_name
      FROM EMPLOYEES e
RIGHT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

Second query re-written using ANSI-92 syntax:

   SELECT e.lastname,
          d.department_name
     FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

In Oracle, in regards to syntax - how do I convert the (+) syntax to modern conventional JOIN?

Without seeing the schema, I find it difficult but this should set you in the right direction:

 FROM apps.po_requisition_lines_all prl 
 INNER JOIN apps.po_requisition_headers_all prha ON prl.requisition_header_id = prha.requisition_header_id 
 INNER JOIN po.po_req_distributions_all     prda ON prda.requisition_line_id  = prl.requisition_line_id
 LEFT  JOIN po.po_distributions_all         pda  ON prda.distribution_id      = pda.req_distribution_id 
-- I note from the example provided that this is a right join
-- Without seeing the schema, it looks to me as though it should be left
-- As I say say, without seeing the schema, I probably shouldn't pass comment
 RIGHT JOIN po.po_headers_all               pha  ON pha.po_header_id          = pda.po_header_id;

For an INNER JOIN you can just say JOIN although I think that explicitly saying INNER aids readability. I also note the example provided has WHERE 1=1 which is redundant.

What does a (+) sign mean in an Oracle SQL WHERE clause?

This is an Oracle-specific notation for an outer join. It means that it will include all rows from t1, and use NULLS in the t0 columns if there is no corresponding row in t0.

In standard SQL one would write:

SELECT t0.foo, t1.bar
  FROM FIRST_TABLE t0
 RIGHT OUTER JOIN SECOND_TABLE t1;

Oracle recommends not to use those joins anymore if your version supports ANSI joins (LEFT/RIGHT JOIN) :

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions […]

Oracle Joins - Comparison between conventional syntax VS ANSI Syntax

Grouping answers together

1. Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins. With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
2. Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS
3. SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
4. Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.
5. Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.
6. Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.
7. Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.

Possible Pitfall in using ANSI syntax on 12c engine

Including a possibility of bug in JOIN in 12c. See here

FOLLOW UP:

Quest SQL optimizer tool rewrites the SQL to ANSI syntax.

SQL convert from Oracle to ANSI JOIN

According to the Oracle documentation:

If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.

So there is an inner join between b and c. And because of the overall conditions, this is going to turn all the joins into INNER JOIN (there needs to be valid values in b and c for that condition to work.

I think the equivalent logic is:

SELECT *
FROM a JOIN
     b
     ON b.id = a.id1 JOIN
     c
     ON c.id = a.id2 AND b.xxx = c.yyyy;

That is, the simple equality eliminates turns the outer joins into inner joins.

Of course, you can test this.

---

Related Topics