Q

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


 

This was first published in June 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close