Old SQL Way for Outer Join?

[smedvid]

I am trying to recall how to perform an outer join via the Where clause. I am modifying another's code and they have opted to use the Where clause everywhere to establish links as opposed to using the LEFT/RIGHT JOINs... It may be a big effort to re-write/Test all of the SQL. I only need to modify a few, but time is short!

I recall something like -
Select a.field1, b.field2
From Tab1 a, Tab2 b
Where a.Field1=(+)b.Field1;

Where I wish to display all records from Tab1 regardless if there is a match in Tab2.

But, it does not seem to work.... Perhaps that was under Oracle SQL. Any ideas on this old SQL Level stuff would be helpful! Thanks! Steve

 

[TomSark]

That's the Oracle way (using the +).

The old way is:
*= for left outer joins
=* for right outer joins

Tom

 

[MalcolmW]

You can also do the logical equivalent of an outer join using UNION and some -- well, it gets messy, especially for more than two tables. However, the only place you are likely to see that type of SQL is for an RDBMS that doesn't support either the *= or += outer join syntax.

 

[TomSark]

However, all SQL dialects that are ANSI92 complaint understand the new way. I recommend that you code it in the new way such that it will be as portable as possible.

Tom

 

[smedvid]

Thanks! I agree, we should keep up with the times, but there is a time crunch at present... Typical.... thanks again... Steve