Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ANSI join syntax
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
![]() |
![]() |