Using ANSI Joins in Oracle Data Integrator (ODI)

Nic Cox
April 23, 2015

By default Oracle Data Integrator (ODI) 11g uses the “old-style” join syntax when joining tables. While many Oracle developers still prefer that style of join, I for one find ANSI joins to be far cleaner and easier to read.

Oracle started supporting ANSI joins in 2001, so it is highly unlikely using these will cause your database to crumble to the ground. While there are ramblings that ANSI joins are now better supported by Oracle’s 12c optimizer, I cannot find concrete evidence either way to support choosing one over the other for performance reasons.

If you want ODI to use ANSI joins instead of the old style,


go to the “Quick Edit” tab in your ODI interface and expand out the “Joins” section. You will see an “Ordered” checkbox to the right of each join. Select this to use ANSI style joins. You will also need to make sure your table joins are ordered correctly. This can be done by using the “Order” column next to the “Ordered” checkbox. You cannot use a mix of join types, so either select or deselect all of the checkboxes.

Here are the differences between the two syntaxes:

Old-Style Join Syntax

  • Relationships are defined in the WHERE clause
  • Outer joins are represented by (+)
SELECT c.customer_name,
FROM customer c, department d, position p
WHERE c.dept_no = d.dept_no
AND c.position_code=p.position_code (+)

ANSI Join Syntax

  • Relationships are defined using the ON clause
  • All joins are named in statement allowing for better readability
SELECT c.customer_name,
FROM customer c
INNER JOIN department d
ON c.dept_no = d.dept_no
LEFT OUT JOIN position p
ON c.position_code=p.position_code

In ODI 12c, you can use ANSI joins by finding and selecting the “Generate ANSI Syntax” checkbox. Yes, Oracle has improved its choice of name in 12c.

Hope this helps, Nic!

We run regular business intelligence courses in both Wellington and Auckland. Find out more here.

Image of Nic Cox with the OptimalBI logo in the background.

Nic gets knee deep in masses of data – he transforms and combines it, to enable better reporting, create useful insights and help shape business decisions. If you let him talk, he’ll probably start rambling on about beer and running.

Connect with Nic on LinkedIn, or read his other blogs here.

Copyright © 2019 OptimalBI LTD.