Ask the Expert

Outer join syntax

Hi. How can I write an Oracle outer join without using the keyword (+)? The idea is to have a query that runs on Oracle and Informix (Informix has a different syntax for an OUTER query). Srini

    Requires Free Membership to View

In Oracle, a typical left outer join looks like this --

select TableA.col1, TableA.col2, TableB.col2
  from TableA, TableB
where TableA.col1 (+) = TableB.col1

Using SQL-92 syntax, which I assume works okay in Informix, this would be --

select TableA.col1, TableA.col2, TableB.col2
  from TableA
left outer join TableB
    on TableA.col1 = TableB.col1

One way around your problem is to write the query the way we had to do it before the SQL-92 syntax --

select TableA.col1, TableA.col2, TableB.col2
  from TableA, TableB
where TableA.col1 = TableB.col1
union all
select TableA.col1, TableA.col2, null
  from TableA
where not exists
       (select *
          from TableB
         where col1 = TableA.col1)

This may not be the most satisfactory solution, because the performance may not be as good as using the appropriate outer join syntax; however, it should work in both databases.

The first subquery is a regular inner join, returning all rows of TableA which have matching rows of TableB. The second subquery provides those rows of TableA which do not have matching rows in TableB. Together, these subqueries return the same result set as the outer join query.

Note that the third column of the second subquery is NULL, to provide the same value that the outer join would. Also, note that UNION ALL can be used instead of UNION because the two subqueries will never have any rows in common.

For More Information

This was first published in June 2001

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: