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.

I think that's called the confuso-join – Shizzmo Mar 11 '10 at 15:07 — Shizzmo, Mar 11 '10 at 15:07

score 22 · Accepted Answer · answered Mar 11 '10 at 15:45

22

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

answered Mar 11 '10 at 15:45

OMG Ponies

325,700
82
523
502

14

The simple rule for reading those is that the column with the `(+)` sign is optional. – Adam Musch Mar 12 '10 at 01:49
2

Oracle's ANSI-92 support began with 9i. – OMG Ponies Mar 16 '10 at 18:28
[That is not a good reference.](https://stackoverflow.com/a/55642928/3404097) Indeed the post is repudiated by the author in the comments. Although in the repudiation they are still confused. – philipxy Sep 10 '19 at 19:33

FrustratedWithFormsDesigner · Answer 2 · 2010-03-13T19:33:28.927

3

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.

edited Mar 13 '10 at 19:33

answered Mar 11 '10 at 15:00

FrustratedWithFormsDesigner

26,726
31
139
202

It's been required in some cases as recently as Oracle 9i -- the Oracle Pro*Cobol precompiler didn't support the `INNER/OUTER/NATURAL/CROSS JOIN` syntax. – Adam Musch Mar 12 '10 at 01:51
A comment on SwisSQL. I downloaded it and gave it a spin, but was very disappointed with the results. It converted only the first of four joins. The join it converted was the one with a *= (I know, not +, but still a funky join), the other three joins where left in the WHERE cluase. – Peter Jan 16 '13 at 16:12

score 2 · Answer 3 · answered Mar 19 '10 at 16:21

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.

score 1 · Answer 4 · answered Mar 11 '10 at 15:06

1

For Oracle 10g

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054012

You can find the other versions of oracle manuals online if your version is different, though the join syntax is probably not different.

answered Mar 11 '10 at 15:06

Shizzmo

16,231
3
23
15

score 1 · Answer 5 · answered Mar 12 '10 at 19:12

1

I seem to remember this syntax going away in the transition from Oracle 8i to 9i -- I think we had a toad plugin that ended up converting everything for us just so we didn't have to waste time going through every query

answered Mar 12 '10 at 19:12

Jason M

512
2
8

score 1 · Answer 6 · answered Mar 19 '10 at 21:38

I don't know of a tool to do the conversion automatically, but even if there were, you would want to review its changes one a case by case basis anyway. Therefore, I don't think a tool would save you much time.

http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm says:

There's no performance benefit or hit by using ANSI joins rather than traditional joins, but by using ANSI joins, your queries are more portable between DBMS platforms, and they're a bit easier to read. In the end, though, it's down to personal preference and whilst there's advantages to the ANSI standard, there's no need to switch if you don't want to.

Also you don't have to use one style or the other everywhere. You can convert your code one query at a time, with some assurance that they will all keep working. I would leave the queries as they are, and resolve to use ANSI SQL-92 syntax in new code.

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

6 Answers6

Linked

Related