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

Question

I have been porting oracle selects, and I have been running across a lot of queries like so:

SELECT e.last_name,
       d.department_name
  FROM employees e,
      departments d
WHERE e.department_id(+) = d.department_id;

...and:

SELECT last_name, 
       d.department_id
  FROM employees e, 
       departments d
 WHERE e.department_id = d.department_id(+);

Are there any guides/tutorials for converting all of the variants of the (+) syntax? What is that syntax even called (so I can scour google)?

Even better.. Is there a tool/script that will do this conversion for me (Preferred Free)? An optimizer of some sort? I have around 500 of these queries to port..

When was this standard phased out? Any info is appreciated.

Answer 1

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

Answer 2

Google "Oracle join syntax". the (+) is used for different flavours of outer joins. I think the first one you showed is a Left Outer Join, and the second one is a Right Outer Join. I haven't seen this notation for quite a while, so I could be a little off, but hopefully, this gives you enough info to hit Google and get the right answer.

UPDATE:

So you wants a tool to do it for you? I have heard that SwisSQL can do something like this, but if most of the queries are simple enough you can probably write a little script that does it for you. OMG Ponies answer nicely shows the pattern for converting from old to new syntax.

Answer 3

This can get quite complicated as the WHERE clause in even simple situations such as

WHERE e.id = d.manager_id(+) OR e.id = d.contact_id(+)

will translate to UNION or subselect query.

If you like python you might take a look at sqlparse, it looks promising and you might get it to do what you need plus some reformatting of the SQL code. It would easily work directly on the source. You'll have to tell it what to do but it does relieve you of writing the boring parsing part.