Re: This outer join thingy...

Repost (seems it failed the last time I tried it...) My $0.02 on the OUTER JOIN: First, some theory, taken from: "A Guide to THE SQL STANDARD", Third Edition, Covers SQL2 by C.J. Date with Hugh Darwen ADDISON-WESLEY Publishing Company "The basic idea behind outer join is as follows. Suppose we are trying to construct the ordinary ("inner") join of two tables T1 and T2. Then any row in either T1 or T2 that matches no row in the other table (under the relevant join condition) simply does not participate in the result. In an outer join, by contrast, such a row does participate in the result --to be more precise, it appears exactly once in the result-- and the column positions that would have been filled with values from some matching row in the other table (if such a row had in fact existed) are filled with nulls instead. Thus, the outer join <<preserves>> nonmatching rows in the result, where the inner join <<loses>> them. A LEFT outer join of T1 with T2 preserves nonmatching rows from T1. A RIGHT outer join of T1 with T2 preserves nonmatching rows from T2. A FULL outer join of T1 with T2 preserves nonmatching rows from both T1 and T2." My understanding: The Informix OUTER JOIN syntax defines an LEFT outer join. select ... from T1, outer T2 but the tables can be reversed to obtain a "RIGHT" outer join. select ... from T2, outer T1 The book goes on to describe: "... let us concentrate on LEFT outer joins ... ... A LEFT JOIN B ON C, (where C is a conditional expression) can perhaps most easily be explained by defining it to be equivalent to the following pseudoSQL expression: select A.*, B.* from A, B where C union all select A.*, null, null, ..., null from A where A.* not in ( select A.* from A, B where C ) In other words, the result consists of the "UNION ALL" of (a) the corresponding inner join and (b) the collection of all rows of A that do not participate in that inner join, each one concatenated with a row of all nulls (one null for each column of B; the pseudoSQL expression "NULL, NULL, ..., NULL" is supposed to be understood as containing one NULL for each column of B). The column names of the result are identical to those of the corresponding inner join. We remark that, in general, the result will include some rows that do not satisfy the conditional expression C." Let me repeat that last statement from the book: WE REMARK THAT, IN GENERAL, THE RESULT WILL INCLUDE SOME ROWS THAT DO NOT SATISFY THE CONDITIONAL EXPRESSION C." As I understand it, given the different scenarios submitted so far as presumable "bugs", Informix is CORRECT in its implementation of the LEFT OUTER JOIN. Catalin -- _____ _____ _____ _____ _ _ _____ Catalin Badea _____/ _____/ _____/ _____/ _/_ _/ _____/ Senior Technical Architect _/ _/___ _/ _/____ _/_/ _/____ Tecsys Inc. _/ ____/ _/ _____/ _/ _____/ _/ _/____ _/___ ____/ _/ ____/ cat@tecsys.com _/ _____/ _____/ _____/ _/ _____/ include system "disclaim.4gh"