Outer joins in Oracle 7 1

[shauntwo]

Hi,

Is there any particular reason related to syntax that would prevent an outer join from executing correctly? I have several tables in my FROM clause, but only two are outer joined. My recordset, however, is restricted only to those records where the joined fields are equal. I can post my particular SQL if necessary, but it's basically like so:

SELECT field1, field2, etc.
FROM table1, table2, table3, table4
WHERE table1.field1 = table2.field1
AND table1.field2 (+) = table3.field1
etc.

where I want to force null fields for my table3 field, even though its primary key may not equal that of table1. This may be more confusing that not, but I'm convinced that I'm missing something fundamental.

Thanks all,
Shaun

 

[karluk]

If I understand you correctly, you should have the (+) on table3, not table1. It's table3 which you expect to have missing ids that need to be returned as null. Try the following query and see if it returns what you are expecting.

SELECT field1, field2, etc.
FROM table1, table2, table3, table4
WHERE table1.field1 = table2.field1
AND table1.field2 = table3.field1 (+)

 

[shauntwo]

Thanks for getting back to me so quickly. Actually, I should have mentioned that I've tried both combinations of the join using either field: in other words, "field1 (+) = field2" and "field1 = field2 (+)". Also, an IT associate suggested that maybe the order of my table sequence in the FROM clause might affect the results, so I've fiddled with that as well, to no avail.

Any other thoughts you might have would be greatly appreciated.

Shaun

 

[karluk]

Well, I can get missing rows returned with the change I suggested, so there must be something more going on than what you're telling us. Are you sure you don't have any conditions in your where clause that force a column in table3 to have a real value?

 

[shauntwo]

Well, I do specify some criteria in the WHERE that whittle down the recordset in table3, but nothing related to the joined fields. Could that be my problem?

 

[karluk]

Absolutely. In an outer join the missing rows of table3 have null assigned to every column. Checking for non-null values in table3 is the same as eliminating the outer join.

 

[shauntwo]

Well, I certainly appreciate your timely feedback. Sounds like I'll have to rework my query, perhaps by using a sub-query for the table3 recordset. I appreciate your great help.

Shaun

 

[karluk]

Suppose you have a condition in your where clause

and table3.xxx = 5

That condition will mess up your outer join because you are insisting on returning only values of 5. If you want to preserve the outer join you have to write

and table3.xxx (+) = 5

That indicates that table3.xxx doesn't really need to have a value of 5. Missing rows are ok too.

Same thing for every other time you reference table3 in your where clause.