Guest User

Untitled

a guest
Oct 17th, 2017
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.65 KB | None | 0 0
  1. ON (t1.a = t2.a and t1.b = t2.b and ... t1.z = t2.z)
  2.  
  3. NVL(t1.a, -1) == NVL(t2.a, -1)
  4.  
  5. on a.x is null and b.x is null or a.x = b.x
  6.  
  7. create table t1(a int, b int, s varchar2(20));
  8. create table t2(a int, b int, s varchar2(20));
  9.  
  10. insert into t1 values(1, 1, '1');
  11. insert into t1 values(2, 2, '2');
  12. insert into t1 values(3, 3, '3');
  13. insert into t1 values(NULL, 1, 'NULL.a.1');
  14. insert into t1 values(NULL, 2, 'NULL.a.2');
  15. insert into t1 values(NULL, 3, 'NULL.a.3');
  16. insert into t1 values(NULL, NULL, 'NULL.both.1');
  17. insert into t1 values(NULL, NULL, 'NULL.both.2');
  18.  
  19. insert into t2 values(1, 1, '1');
  20. insert into t2 values(2, 2, '2');
  21. insert into t2 values(NULL, 1, 'NULL.a.1');
  22. insert into t2 values(NULL, 2, 'NULL.a.2');
  23. insert into t2 values(NULL, NULL, 'NULL.both.1');
  24. insert into t2 values(NULL, NULL, 'NULL.both.2');
  25.  
  26. SQL> select * from t1 join t2 on NVL(t1.a, -1) = NVL(t2.a, -1) and NVL(t1.b, -1) = NVL(t2.b, -1);
  27.  
  28. A B S A B S
  29. ---------- ---------- -------------------- ---------- ---------- --------------------
  30. 1 1 1 1 1 1
  31. 2 2 2 2 2 2
  32. 1 NULL.a.1 1 NULL.a.1
  33. 2 NULL.a.2 2 NULL.a.2
  34. NULL.both.1 NULL.both.2
  35. NULL.both.1 NULL.both.1
  36. NULL.both.2 NULL.both.2
  37. NULL.both.2 NULL.both.1
  38.  
  39. 8 rows selected.
Add Comment
Please, Sign In to add comment