This discussion is archived
14 Replies Latest reply: May 25, 2010 3:41 PM by 775189 RSS

Joins using tuples (a.x, a.y) = (b.x, b.y) possible?

775059 Newbie
Currently Being Moderated
Hello,

I'd like to make a join between tables including handling of null-values.

Example:

create table test1 (id1 number not null, id2 number);
insert into test1 values (1,1);
insert into test1 values (2,null);

create table test2 (id1 number not null, id2 number);
insert into test2 values (1,1);
insert into test2 values (2,null);

select *
from test1, test2
where (test1.id1, test1.id2)=(test2.id1, test2.id2)
;
ERROR at line 3:
ORA-00920: invalid relational operator

I know that I can hand-code this to make it work:

select *
from test1, test2
where test1.id1=test2.id1
and (test1.id2=test2.id2 or test1.id2 is null and test2.id2 is null)
;


ID1 ID2 ID1 ID2
---- ---- ---- ---- ---- ---- --- ---- -
1 1 1 1
2 2


My question: Does someone have an idea to make this shorter, or is it already the best way?

Best regards,
Martin
  • 1. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Martin,

    The IN operator can compare tuples, like this:
    where    (test1.id1, test1.id2)  IN ((test2.id1, test2.id2))
    The nested parentheses after the keyword IN are required. The outer set encloses the list of tuples (there happens to be only one in this case), and each tuple in the list is enclosed in another set.

    IN performs an = operation for each member of the tuples. Remember that x = NULL is neither TRUE nor FALSE, regardless of what x is, so the condition above will be not be TRUE is any of the ids are NULL.

    Edited by: Frank Kulash on May 25, 2010 8:37 AM
  • 2. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    774875 Newbie
    Currently Being Moderated
    SELECT test1.id1, test1.id2, test2.id1, test2.id2
    FROM test1, test2
    WHERE (DECODE (test1.id1, NULL, 1, test1.id1),
    DECODE (test1.id2, NULL, 1, test1.id2)
    ) IN
    ((DECODE (test2.id1, NULL, 1, test2.id1),
    DECODE (test2.id2, NULL, 1, test2.id2)
    )
    )
  • 3. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    680268 Newbie
    Currently Being Moderated
    You can use concat operator as well.


    SELECT test1.id1, test1.id2, test2.id1, test2.id2
    FROM test1, test2
    WHERE DECODE (test1.id1, NULL, 'X', test1.id1)||DECODE (test1.id2, NULL, 'X', test1.id2)=
    DECODE (test2.id1, NULL, 'X', test2.id1)||DECODE (test2.id2, NULL, 'X', test2.id2)
  • 4. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    Marwim Expert
    Currently Being Moderated
    No, because 'a'||'ab' would match 'aa'||'b'

    Regards
    Marcus
  • 5. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    Nicosa Expert
    Currently Being Moderated
    Using such concatenation can lead to error.
    imagine following data :
    SQL> select * from test1;
    
    ID1   ID2
    ----- -----
    AA    B
    A     AB
    
    SQL> select * from test2;
    
    ID1   ID2
    ----- -----
    AA    B
    Only 1 line common to both tables test1 and test2, but using concatenation comparison :
    SQL> SELECT test1.id1, test1.id2, test2.id1, test2.id2
      2  FROM test1, test2
      3  WHERE DECODE (test1.id1, NULL, 'X', test1.id1)||DECODE (test1.id2, NULL, 'X', test1.id2)=
      4  DECODE (test2.id1, NULL, 'X', test2.id1)||DECODE (test2.id2, NULL, 'X', test2.id2);
    
    ID1   ID2   ID1   ID2
    ----- ----- ----- -----
    AA    B     AA    B
    A     AB    AA    B
    2 lines returned...
    You cannot use concat unless you us some separator that CANNOT be in concatenated columns....

    Edited by: Nicosa on May 25, 2010 6:09 AM
  • 6. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    BluShadow Guru Moderator
    Currently Being Moderated
    Marwim wrote:
    No, because 'a'||'ab' would match 'aa'||'b'
    Not to mention it causing indexes to be ignored. ;)
  • 7. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Nicosa wrote:
    ... You cannot use concat unless you us some separator that CANNOT be in concatenated columns....
    Actually, there are some special cases where that that's not true.
    For example, if the two columns have unequal fixed lengths. (Say id1 is always 1 character long, and id2 is always 2 characters) The length of the concatenated string tells which items are NULL.
    Another example is if one of the columns is self-delimiting. For example, if id1 always ends in a numeral, but id2 never contains a numeral.

    This is known as the "ab asse crevit" problem.
  • 8. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    775059 Newbie
    Currently Being Moderated
    Hello,
    thank your for your input.
    The approach has the issue that you need to have a value which never appears in the data, otherwise it can deliver wrong results:
    create table test1 (id1 number not null, id2 number);
    insert into test1 values (1,1);
    insert into test1 values (2,null);
    
    create table test2 (id1 number not null, id2 number);
    -- NOTE THIS - changed id2 to null
    insert into test2 values (1,null); 
    insert into test2 values (2,null);
    
    SELECT test1.id1, test1.id2, test2.id1, test2.id2
    FROM  test1, test2
    WHERE (DECODE (test1.id1, NULL, 1, test1.id1),
    DECODE (test1.id2, NULL, 1, test1.id2)
    ) IN
    ((DECODE (test2.id1, NULL, 1, test2.id1),
    DECODE (test2.id2, NULL, 1, test2.id2)
    )
    );
    
           ID1        ID2        ID1        ID2
    ---------- ---------- ---------- ----------
             1          1          1
             2                     2
    (Match when test1.ID2=1 and test2.ID2 is null is wrong)
  • 9. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    775059 Newbie
    Currently Being Moderated
    Thank you for the many responses.

    I've found the same syntax as in the IN operator can be used with = (thank's to Frank's response):
    select *
    from test1, test2
    where (test1.id1, test1.id2)=((test2.id1, test2.id2))
    ;
    However: As mention by frank as well - the result does not handle null values.

    So I need to keep my original way of writing:
    select *
    from test1, test2
    where test1.id1=test2.id1
     and (test1.id2=test2.id2 or test1.id2 is null and test2.id2 is null)
    ;
  • 10. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    Nicosa Expert
    Currently Being Moderated
    Frank Kulash wrote:
    Actually, there are some special cases where that that's not true.
    Indeed, you are right.
    I should have written "you cannot use concat unless you know there won't be cases where different concatened tuple will give the same string".

    I cannot find stuff searching "ab asse crevit", do you have some interesting link to share ?
  • 11. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Nicosa wrote:
    Frank Kulash wrote:
    Actually, there are some special cases where that that's not true.
    Indeed, you are right.
    I should have written "you cannot use concat unless you know there won't be cases where different concatened tuple will give the same string".
    I should have stressed that your point is valid: concatenating values like that for comparison is usually a bad idea.
    I cannot find stuff searching "ab asse crevit", do you have some interesting link to share ?
    Sorry, I can't find a good link.

    "Ab asse crevit" means, literally, "He grew from a penny". (The as was a small Roman coin). The first century author Petronius, in his Satyricon used the phrase to describe someone who made his own fortune, starting with next to nothing. In Petronius' day, Latin was often written without any spaces in between the words, so this was written 'ABASSECREVIT'. Later, when spacing between words was more common, this was carelessly transcribed as 'ABAS SECREVIT' (meaning "the abbot hid"), which is perfectly good Latin out of context, but makes absolutely no sense in context. This flawed reading was copied for centuries, and only in modern times did people guess what the real meaning was, and how the error occurred.
  • 12. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    Nicosa Expert
    Currently Being Moderated
    Frank Kulash wrote :
    I should have stressed that your point is valid: concatenating values like that for comparison is usually a bad idea.
    We boh agree on that point.
    Thanks for the info on Petronius.
  • 13. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    775189 Newbie
    Currently Being Moderated
    Try the LNNVL() function. You might sometimes see it in an explain plan using dbms_xplan when Oracle rewrites or adds on to your query but you can use it yourself.
    It can only be used in a Where clause and is at first somewhat confusing because the parameter is an expression of the logical opposite of what you want.
    But this is actually a good case to exemplify what it does.

    From the docs:
    {font: Courier}
    LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, even in contexts where the IS [NOT] NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls.

    Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.
    {font}

    Andrew

    sql->drop table test1;
    
    Table dropped.
    
    Elapsed: 00:00:00.04
    sql->create table test1 (id1 number not null, id2 number);
    
    Table created.
    
    Elapsed: 00:00:00.02
    sql->insert into test1 values (1,1);
    
    1 row created.
    
    Elapsed: 00:00:00.00
    sql->insert into test1 values (2,null);
    
    1 row created.
    
    Elapsed: 00:00:00.00
    sql->
    sql->drop table test2;
    
    Table dropped.
    
    Elapsed: 00:00:00.04
    sql->create table test2 (id1 number not null, id2 number);
    
    Table created.
    
    Elapsed: 00:00:00.01
    sql->insert into test2 values (1,1);
    
    1 row created.
    
    Elapsed: 00:00:00.00
    sql->insert into test2 values (2,null);
    
    1 row created.
    
    Elapsed: 00:00:00.01
    sql->
    sql->
    sql->select *
      2    from test1, test2
      3   where test1.id1   = test2.id1
      4     and lnnvl(test1.id2 != test2.id2)
      5  /
    
           ID1        ID2        ID1        ID2
    ---------- ---------- ---------- ----------
             1          1          1          1
             2                     2
  • 14. Re: Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
    775189 Newbie
    Currently Being Moderated
    I missed your additional criteria that a value can not match a null.
    In that case the lnnvl() function will not work and the best sql is the one you started with.
    sql->select *
      2    from test1, test2
      3   where test1.id1   = test2.id1
      4     and lnnvl(test1.id2 != test2.id2)
      5  /
    
           ID1        ID2        ID1        ID2
    ---------- ---------- ---------- ----------
             1          1          1          1
             1          1          1
             2                     2
    
    Elapsed: 00:00:00.01
    sql->
    sql->select *
      2    from test1, test2
      3   where test1.id1   = test2.id1
      4     and (test1.id2 = test2.id2 or test1.id2 is null and test2.id2 is null)
      5  /
    
           ID1        ID2        ID1        ID2
    ---------- ---------- ---------- ----------
             1          1          1          1
             2                     2

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points