RE: ANSI join syntax
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Apr 2005 15:00:38 -0500
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@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
