Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2008
    Posts
    6

    Unanswered: Outer Join condition on Null values

    I execute an SQL with Left Outer-Join condition
    on several common fields.
    one of those fields is of type string ("VarChar2"),
    and may include null-strings.
    Now, as long as there are equal strings in records
    of the 2 tables, the join condition works as I expect
    and matches the records.
    The problem is that I expect records with null string
    in both tables to be "Matched" as well,
    but it doesn't happen.

    I'll demonstrate it with a simple example:
    SELECT T2.SomeField
    FROM T1, T2
    WHERE (T1.JoinStrField = T2.JoinStrField (+))
    AND (T1.OtherJoinField = T2.OtherJoinField(+))


    Suppose T1 has the record
    JoinStrField = <Null> ; OtherJoinField = 1
    and T2 has the record
    JoinStrField = <Null> ; OtherJoinField = 1 ; SomeField = "Something"

    The result-set will be one record: SomeField = <Null>
    What I expect that record to be: SomeField = "Something"

    If anyone can help me to improve my SQL ,
    I would really appreciate it!
    Just one comment,
    I use "Outer-Join" in order to achieve fast performance (very big tables),
    so I would like to avoid of any kind of "slow" solutions such as sub-queries.

    Thanks!
    Last edited by daido1; 02-20-08 at 08:59.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The way you've put it, in order to fetch "Something" the WHERE clause should be
    Code:
    where t1.otherjoinfield (+) = t2.otherjoinfield  
      and t1.joinstrfield   (+) = t2.joinstrfield

  3. #3
    Join Date
    Feb 2008
    Posts
    6
    LittleFoot,
    Thanks for responding my thread,
    but unfortunately the query you propose does not
    solve my problem.
    I need the outer join on the right table, not left.
    Anyway, it doesn't matter if the join is right or left,
    using this kind of SQL "Null" values in the Joined fields
    are anyway not matched.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Hi
    First things ... First. You have said that you are using a Right Join, but your code uses a Left join.
    Second. You may find it a bit easier to actually supply a test case. i.e. supplky create table and insert scripts to duplicate your issue. Then tell us what results you expect based on the test case you provided.

  5. #5
    Join Date
    Feb 2008
    Posts
    6
    Dear Pablolee,

    Thanks for your correction & suggestions,
    I found them quite helpful!
    I just corrected my mistake about the JOIN ("left" instead of "right").
    Regarding your suggestion to supply "Create Table" & "Insert"
    statements, I'll keep doing that on my next threads.
    (unless my explanation on the current thread is not clear enough,
    then I'll supply such scripts to current thread as well...)

    Best Regards!

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    I have looked at this a few times and I definitely think that it would be easier if you provide a test case.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd agree with you; that's why I said that - knowing what was presented - the solution *might* be
    Code:
    SQL> select * from t1;
    
    SOMEFIELD            JOINSTRFIELD         OTHERJOINFIELD
    -------------------- -------------------- --------------------
                                              1
    
    SQL> select * from t2;
    
    SOMEFIELD            JOINSTRFIELD         OTHERJOINFIELD
    -------------------- -------------------- --------------------
    something                                 1
    
    SQL> select t2.somefield
      2  from t1, t2
      3  where t1.otherjoinfield (+) = t2.otherjoinfield
      4    and t1.joinstrfield   (+) = t2.joinstrfield
      5  ;
    
    SOMEFIELD
    --------------------
    something
    
    SQL>

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    But then, we would be as well doing:
    SELECT somefield
    FROM t2;
    Which is the problem when someone pseudocodes and oversimplifies

  9. #9
    Join Date
    Feb 2008
    Posts
    6
    Quote Originally Posted by pablolee
    I have looked at this a few times and I definitely think that it would be easier if you provide a test case.
    Fine, I guess Pablolee convinced me...

    Assume the following Schema...
    Code:
    CREATE TABLE T1
    (
      OTHER_JOIN_FIELD  NUMBER(5)                   NOT NULL,
      JOIN_STR_FIELD    VARCHAR2(10)
    );
    
    CREATE TABLE T2
    (
      OTHER_JOIN_FIELD  NUMBER(5)                   NOT NULL,
      JOIN_STR_FIELD    VARCHAR2(10),
      SOME_FIELD        VARCHAR2(10)                NOT NULL
    )
    And the following Data...
    Code:
    INSERT INTO T1 ( OTHER_JOIN_FIELD, JOIN_STR_FIELD ) VALUES ( 
    0, NULL); 
    INSERT INTO T1 ( OTHER_JOIN_FIELD, JOIN_STR_FIELD ) VALUES ( 
    1, 'ONE'); 
    INSERT INTO T2 ( OTHER_JOIN_FIELD, JOIN_STR_FIELD, SOME_FIELD ) VALUES ( 
    0, NULL, 'Result_0'); 
    INSERT INTO T2 ( OTHER_JOIN_FIELD, JOIN_STR_FIELD, SOME_FIELD ) VALUES ( 
    1, 'ONE', 'Result_1');
    Then execute the Query ...
    Code:
    SELECT T2.SOME_FIELD 
    FROM T1 , T2
    WHERE (T1.OTHER_JOIN_FIELD = T2.OTHER_JOIN_FIELD (+))
      AND (T1.JOIN_STR_FIELD = T2.JOIN_STR_FIELD (+))
    The Actual Result is...

    Code:
    Some_Field
    ----------
    <Null>
    Result_1
    But the result I desire is...
    Code:
    Some_Field
    ----------
    Result_0
    Result_1
    Anyone can help please??
    Thanks alot!

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    And again it is
    Code:
    SQL> SELECT T2.SOME_FIELD
      2  FROM T1 , T2
      3  WHERE T1.OTHER_JOIN_FIELD (+) = T2.OTHER_JOIN_FIELD
      4    AND T1.JOIN_STR_FIELD   (+) = T2.JOIN_STR_FIELD;
    
    SOME_FIELD
    ----------
    Result_1
    Result_0
    
    SQL>
    What's wrong with it, again?

  11. #11
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by daido1
    The Actual Result is...
    Not on my system it's not:
    Code:
    SQL> SELECT T2.SOME_FIELD
      2  FROM T1 , T2
      3  WHERE (T1.OTHER_JOIN_FIELD = T2.OTHER_JOIN_FIELD(+) )
      4    AND (T1.JOIN_STR_FIELD   = T2.JOIN_STR_FIELD(+) );
    
    SOME_FIELD
    ----------
    Result_1
    I would imagine that this is because you are over simplifying the situation (Littlefoot's solution from earlier would give you your desired results if this were an accurate reflection of your situation.)
    Beaten to it every time Littlefoot

  12. #12
    Join Date
    Feb 2008
    Posts
    6
    Quote Originally Posted by pablolee
    Littlefoot's solution from earlier would give you your desired results if this were an accurate reflection of your situation.
    Beaten to it every time Littlefoot
    Littlefoot's solution indeed gave me the desired results,
    but with the wrong join...
    He just swapped the "(+)" from one side to the other,
    so obviously "SomeField" will result a Not-Null value since it
    doesn't function as a lookup-field anymore.
    The whole idea is to lookup "SomeField" using a one-side
    Outer-Join where T1 is the Master-Table and T2 is the Lookup-Table.
    Please execute the test case I submitted before and tell me
    if you understand my problem.
    By the way , I tried to work-around this problem using "NVL" as following:
    Code:
    SELECT T2.SOME_FIELD
    FROM T1 , T2
    WHERE (T1.OTHER_JOIN_FIELD = T2.OTHER_JOIN_FIELD (+))
     AND (NVL (T1.JOIN_STR_FIELD,'x') = NVL(T2.JOIN_STR_FIELD,'x') (+))
    But then I get an error-message :
    "ORA00936 - Missing Expression"
    If I only omit the "(+)" from the query it works, but then
    it's an Inner-Join which I do NOT desire.
    All I need is a simple Outer-Join that uses T1
    as the master table and T2 as the lookup table,
    And although one of the join fields ("Join_Str_Field") allows "Null",
    I want those "Nulls" to pass the Join test.
    They currently don't!
    Last edited by daido1; 02-20-08 at 10:22.

  13. #13
    Join Date
    Feb 2008
    Posts
    6
    Great News everybody - I solved it on my own !!

    Well, my "NVL" trick is indeed a very good solution !
    In my previous post, I just had a small syntax error -
    I shoud have insert the "(+)" into the brackets..

    so eventually the solution for my problem is ...
    Code:
    SELECT T2.SOME_FIELD
    FROM T1 , T2
    WHERE (T1.OTHER_JOIN_FIELD = T2.OTHER_JOIN_FIELD (+))
     AND (NVL (T1.JOIN_STR_FIELD,'x') = NVL(T2.JOIN_STR_FIELD (+),'x') )
    I just tested it, and it does give me the expected result -

    Code:
    SOME_FIELD
    ------------
    Result_0
    Result_1
    (again, notice that only with this SQL
    "Some_Field" functions properly as a lookup field although
    one of the FKs contain nulls..)

    If anyone thought about a different kind of solution,
    Ofcourse, I'll be glad to see!

    Anyway, thanks alot to everyone who tried to help!

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The problem is that a NULL = NULL will always be false. NULL is not a value, it is nothing and nothing can never equal nothing.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •