From oracle-l-bounce@freelists.org Wed Apr 6 18:00:46 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j36N0kep019238 for ; Wed, 6 Apr 2005 18:00:46 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j36N0jem019234 for ; Wed, 6 Apr 2005 18:00:45 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BC91991E57; Wed, 6 Apr 2005 16:58:41 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29443-04; Wed, 6 Apr 2005 16:58:41 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3DD03913DD; Wed, 6 Apr 2005 16:58:41 -0500 (EST) X-ME-UUID: 20050406215651885.D82DC1C00087@mwinf0712.wanadoo.fr Message-ID: <42545B1F.6060001@roughsea.com> Date: Wed, 06 Apr 2005 23:56:47 +0200 From: Stephane Faroult Organization: RoughSea Limited User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040804 X-Accept-Language: en, fr-fr, en-us MIME-Version: 1.0 To: paul.baumgartel@gmail.com Cc: "Oracle-L (E-mail)" Subject: Re: ANSI join syntax References: In-Reply-To: Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 18139 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sfaroult@roughsea.com Precedence: normal Reply-To: sfaroult@roughsea.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: Paul, What the optimizer does behind your back, I don't know. But I guess that it is as least trying to respect the spirit of the ANSI join, which is, as far as I understand it, to make a clear distinction between what the conditions which allow to join two tables together and the conditions which are supposed to filter the resulting output. You can of course sometimes consider that you are joining to a subset of another table, and push some 'local' filtering conditions to the JOIN clause - somehow, it is pretty close to inline views. But it doesn't work with IS NULL conditions on left (outer) joins, because NULL columns pop out of nowhere precisely as a result of the join. No great fan of ANSI joins either (I sympathize with the intent, though). The only quality I have found to them so far is that they make building complex queries dynamically *slightly* easier. Stephane Faroult Paul Baumgartel wrote: >I confess that I don't use the ANSI join syntax much (probably because >I don't write much SQL any more). My question involves the presence >of non-join predicate clauses in the ON part of a join clause. I have >a developer who complains that this query gives him incorrect results >(i.e., more than one row): > >SELECT c.company_fk, cs.store_fk >FROM > company c >LEFT JOIN store cs > ON cs.company_fk = c.company_fk > AND (c.effective_date IS NULL OR c.effective_date <= >cs.effective_date ) > AND (c.expiration_date IS NULL OR c.expiration_date > >cs.effective_date ) >WHERE > cs.store_fk = 18793 > >It returns 8 rows, only one of which has a value (18793) for store_fk; >the other rows have a null store_fk. > >I replied, what happens when you run > >SELECT c.company_fk, cs.store_fk >FROM > company c >LEFT JOIN store cs > ON cs.company_fk = c.company_fk >WHERE > cs.store_fk = 18793 > AND (c.effective_date IS NULL OR c.effective_date <= >cs.effective_date ) > AND (c.expiration_date IS NULL OR c.expiration_date > >cs.effective_date ) > >where the non-join predicates are where they belong. That query >returns one row, as expected. > >How, then, does Oracle evaluate the additional predicate clauses in >the ON part of the join? > >Thanks. > > > -- http://www.freelists.org/webpage/oracle-l