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


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


Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.