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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top