Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ANSI join syntax

RE: ANSI join syntax

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Thu, 7 Apr 2005 15:00:38 -0500
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE66970235E1@QTEX1.qg.com>


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_at_quadtechworld.com      QuadTech, Sussex, WI USA



-----Original Message-----

From: oracle-l-bounce_at_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 Received on Thu Apr 07 2005 - 18:40:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US