FAQ
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.

--
Paul Baumgartel
paul.baumgartel_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Stephane Faroult at Apr 6, 2005 at 6:00 pm
    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
  • Jonathan Gennick at Apr 6, 2005 at 9:45 pm
    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://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

    Join the Oracle-article list and receive one
    article on Oracle technologies per month by
    email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article,
    or send email to Oracle-article-request_at_gennick.com and
    include the word "subscribe" in either the subject or body.

    Wednesday, April 6, 2005, 5:06:15 PM, Paul Baumgartel (paul.baumgartel_at_gmail.com) wrote:

    PB> I confess that I don't use the ANSI join syntax much (probably because
    PB> I don't write much SQL any more). My question involves the presence
    PB> of non-join predicate clauses in the ON part of a join clause. I have
    PB> a developer who complains that this query gives him incorrect results
    PB> (i.e., more than one row):

    PB> SELECT c.company_fk, cs.store_fk
    PB> FROM
    PB> company c
    PB> LEFT JOIN store cs
    PB> ON cs.company_fk = c.company_fk
    PB> AND (c.effective_date IS NULL OR c.effective_date <=
    PB> cs.effective_date )
    PB> AND (c.expiration_date IS NULL OR c.expiration_date >
    PB> cs.effective_date )

    PB> WHERE

    PB> cs.store_fk = 18793

    PB> It returns 8 rows, only one of which has a value (18793) for store_fk;
    PB> the other rows have a null store_fk.

    PB> I replied, what happens when you run

    PB> SELECT c.company_fk, cs.store_fk
    PB> FROM
    PB> company c
    PB> LEFT JOIN store cs
    PB> ON cs.company_fk = c.company_fk
    PB> WHERE
    PB> cs.store_fk = 18793
    PB> AND (c.effective_date IS NULL OR c.effective_date <=
    PB> cs.effective_date )
    PB> AND (c.expiration_date IS NULL OR c.expiration_date >
    PB> cs.effective_date )

    PB> where the non-join predicates are where they belong. That query
    PB> returns one row, as expected.

    PB> How, then, does Oracle evaluate the additional predicate clauses in
    PB> the ON part of the join?

    PB> Thanks.
  • Paul Baumgartel at Apr 6, 2005 at 9:57 pm
    Jonathan,

    Thank you! That is exactly what I was looking for, and it makes perfect sense.

    Regards,

    Paul
    On Apr 6, 2005 9:42 PM, Jonathan Gennick wrote:
    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
  • Jared Still at Apr 7, 2005 at 1:46 pm
    Excellent article Jonathan, thanks for pointing it out.
    Jared
    On Apr 6, 2005 5:42 PM, Jonathan Gennick wrote:

    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
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Jesse, Rich at Apr 7, 2005 at 6:40 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 6, '05 at 5:12p
activeApr 7, '05 at 6:40p
posts6
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase