Re: Outer Join Syntax - Quick Question

No question on OUTER JOIN is ever going to be quick... Marc Billiet <Marc.Billiet@alcatel.be> wrote: }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). Since it was written as an equi-join, then it will either return the row or rows in Another_Table where the B value is the same as the value in Some_Table.A, or it will return a row where the values in the Another_Table columns are all NULL. Basically the same as what you were saying, but (I hope) a little clearer. You can also use compound join criteria and joins other than equi-joins; you can also have multiple OUTER joins in a single query: SELECT A.*, B.*, C.*, D.* FROM A, OUTER B, OUTER (C, OUTER D) WHERE ... (See, for example, Appendix G in the I4GL Reference Manual, Vol 2, Version 4). }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. In Informix, you could write: SELECT some_table.a, another_table.b, another_table.c FROM some_table, OUTER 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)) However, this would not necessarily produce exactly the result you would expect. Informix does the outer part of the join later in the process than I think is sensible, but the actual behaviour has been documented for a long time (more than 7 years) so it is deemed correct. Specifically, the filter conditions on the outer-joined table are treated such that if a row in Some_Table matches a row in Another_Table on the join, but the combined row would be rejected because of a filter condition on the columns in Another_Table, then the row from Some_Table is still selected, but it is joined with nulls in place of the values from Another_Table. This sort of corresponds to the sequence: JOIN, RESTRICT, OUTER, PROJECT. I think it would be more sensible (more useful) if the operations were done in the sequence: OUTER JOIN, RESTRICT, PROJECT However, that's a lost battle. One disadvantage of the Oracle notation is that you can't tell from the FROM clause whether the statement involves an outer join or not; the same comment applies to the Sybase/Microsoft notation too. One major disadvantage of every one of these notations is that they are all distinctly different again from the SQL-92 notation. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path.