I am responsible for converting an MS Sql database so that it can be imported to multiple database systems (Hypersonic, Postresql, Oracle, etc).
The problem is that a lot of the Ms Sql queries use a variety of outer joins.
I understand that Ms Sql is compliant with the Ansi SQL'92 standard (entry level plus parts of the other levels). I thought Oracle was too.
I have recently found out that there is no simple way to convert the outer joins from Ms Sql to Oracle. Is there any way at all to convert outer joins (ie: unions, intersections, etc)?
Any help would be appreciated.
Thank you in advance.
Joins, unions and intersections can be done in Oracle also. Concerning outer joins, Oracle implements join functionality by allowing "left" or "right" outer joins. That is, a join can contain all records from one table and records with a matching key field from another table. However, using join syntax, you cannot select matching records from both tables AND unique records from both tables in one join query. However, Union could be used for this purpose. Essentially union and intersection have the same meanings in Oracle as in any set theory.
To accomplish a join in Oracle, let us assume you have a master table with a primary key, and a child table with a related foreign key. The master could have records that do not have associated records in the child. Let's create a query which selects ALL master records whether they have any child records or not:
select master.id, master.name, master.birthday, child.phonenumber, child.address, child.recordtype from master M, child C where M.id=C.id(+);
The (+) syntax is applied to one or more fields in a where clause, and is placed next to the field in a table which may or may not contain related records. If a master record is located, but no related detail record is found, the fields which should come from the child in this query will be returned with NULL values. The master record will be returned with the values in that record in the master table.
Essentially, the (+) is placed next to a field where data might not be found. I remember this with the following logic:
The (+) is placed on the deficient field, and tells oracle to add NULL values to that field to make the query work.
You cannot place the join symbol on both tables in a where clause to get a "full outer join". However, you can use the (+) on multiple fields to properly relate tables that have composite keys.
I edited this post to add a "," between the "M" and "child" in my join select above . . .
[Edited by kmesser on 04-13-2001 at 11:56 AM]
Thank you kmesser,
That is very useful information.
Is there a common sql syntax that will work under both ms sql and oracle?
I don't know enough about MS SQL SERVER to help you reliably. I'm sure you'll get some help here though.