Joins using tuples (a.x, a.y) = (b.x, b.y) possible?
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