Re: Outer Join Syntax - Quick Question
Daniel Wright wrote:
>
> EGPWells wrote:
>
> > Could someone please tell me what then syntax is for an outer join
> > using
> > Informix?
>
> For Informix, simply put "OUTER" in front of the table to be
> outer-joined to, as in
> SELECT a,b,c
> FROM some_table, OUTER another_table> ...
> This makes a lot more sense than the *= and =* used by Sybase and
> Microsoft's SQLServer. (at least to me anyway).
I don't know much about Informix, so correct me if I'm wrong (what follows can be
completely wrong if I don't understand the Informix outer join syntax).
In Oracle, the outer join is specified with (+) after the column you want to join. I
think it is much more flexible.
If you write something like:
select some_table.a,another_table.b, another_table.c
from some_table, outer another_Table b
where some_table.a = another_table.b
another_table.b will always have the same value as some_table.a in the where-clause,
even if the row in another_table doesn't exist (that's in fact the point of the
outer-join). In Oracle you can specify for each column occurence if it should be outer
joined, so you can write something like (just a stupid example because I couldn't find a
real one, although I'm sure I already used the same principle):
select some_table.a, another_table.b, another_table.c
from some_table, another_table
where some_Table.a = another_table.b(+)
and ((another_table.b is not null and another_table.c > some_table.d)
or (another_table.b is null))
Another_table is outer joined with some_table, but I still know in the where-clause
whether the row exists in another_table or not, by checking the value of
another_table.b without the (+). So, if the row exists in both tables, column
another_table.c should be greater than some_table.d, if not, the value of column d of
some_Table doesn't matter.
Marc