From oracle-l-bounce@freelists.org Thu Apr 7 18:40:41 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j37NefNq016743 for ; Thu, 7 Apr 2005 18:40:41 -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 j37Nebem016738 for ; Thu, 7 Apr 2005 18:40:38 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0DD6D92D0F; Thu, 7 Apr 2005 15:04:26 -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 10831-05; Thu, 7 Apr 2005 15:04:26 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8668492CDC; Thu, 7 Apr 2005 15:04:25 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain Subject: RE: ANSI join syntax Date: Thu, 7 Apr 2005 15:00:38 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: ANSI join syntax thread-index: AcU7E1XV9v6MLriFTvarqUp7DNwFsAAkgsBQ From: "Jesse, Rich" To: "Oracle-L" X-OriginalArrivalTime: 07 Apr 2005 20:00:06.0063 (UTC) FILETIME=[64BFC3F0:01C53BAC] Content-Transfer-Encoding: 8bit X-archive-position: 18197 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Rich.Jesse@quadtechworld.com Precedence: normal Reply-To: Rich.Jesse@quadtechworld.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: Perfect timing on this as I've just transformed a 9-table query into the ANSI syntax in an attempt to understand it better for tuning (and for curiosity). I transformed this shortened "regular" SQL example: SELECT sl.so_num, sl.line_no, sb.master FROM so_line sl, so_bom sb WHERE sl.vendor = 'RICH' AND sl.so_num = sb.so_num(+) AND ' ' = sb.po_num(+) ORDER BY 1, 2, 3; into: SELECT sl.so_num, sl.line_no, sb.master FROM so_line sl, LEFT OUTER JOIN so_bom sb ON sl.so_num = sb.so_num WHERE sl.vendor = 'RICH' AND ' ' = sb.po_num(+) ORDER BY 1, 2, 3; This generated an error because the "(+)" can't be mixed in with ANSI syntax. Given Jonathan's article, I wouldn't move that into the FROM clause. So, how's one supposed to specify a filter on an OUTER JOINed table in ANSI syntax? Using "sb.po_num IS NULL OR sb.po_num = ' '"? That's going to be a little verbose for the large queries... Rich Rich Jesse System/Database Administrator rich.jesse@quadtechworld.com QuadTech, Sussex, WI USA -----Original Message----- From: oracle-l-bounce@freelists.org Sent: Wednesday, April 06, 2005 8:42 PM To: Paul Baumgartel Cc: Oracle-L Subject: Re: ANSI join syntax Hello Paul, It *can* make a difference when you move a predicate from the WHERE clause into the JOIN clause. (It surprised me too) Have a look at the following article: http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html So far as I've ever been able to determine, moving a predicate between WHERE and JOIN can only possibly make a difference when outer-joins are involved. In essense, if you "say" it is a join predicate, then SQL treats it like one. Again, as I admit in the article, I was caught out once myself by this behavior. Best regards, Jonathan Gennick --- Brighten the corner where you are -- http://www.freelists.org/webpage/oracle-l