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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
This was first published in June 2001