Re: Outer join question

Guillermo Labatte wrote: > I issue the command ... > > select * from table_A,outer table_B > where table_A.x=table_B.y > into temp table_C; > select * from table_C where y is null> > and it returns a different result from > > select * from table_A,outer table_B > where table_A.x=table_B.y and > table_B is null; Typo here. I assume you mean "table_B.y is null" > Why? I haven't tested, but would venture that in the 2nd case, the test "table_B.y IS NULL" is tested first before the join, so no joins are made (since NULL doesn't equal NULL), and you're just getting all rows of table_A. In the first case, you're getting a legitimate join, which could give multiple representations of the same table_A row if more than one row of table_B joins to it, and the select from the TEMP table will only give you those rows in table_A that do not have rows in table_B. The same result could be had, I would venture, in: SELECT * FROM table_A WHERE table_A.x NOT IN (SELECT UNIQUE y FROM table_B); Try SET EXPLAIN ON and see if any clues are given. -- //////////////// ======================================================= ////////// // Dennis J. Pimple Informix Software, Inc. ////// / /// Principal Consultant 6300 S Syracuse Way Ste 205 ///// // //// dennisp@informix.com Englewood CO 80111 //// // ///// /// // ////// recept: 303-850-0210 // // /////// direct: 303-740-5611 Opinions expressed are mine, / /////////// fax: 303-779-4025 and do not necessarily //////////////// http://www.informix.com reflect those of my employer