Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ON (t1.a = t2.a and t1.b = t2.b and ... t1.z = t2.z)
- NVL(t1.a, -1) == NVL(t2.a, -1)
- on a.x is null and b.x is null or a.x = b.x
- create table t1(a int, b int, s varchar2(20));
- create table t2(a int, b int, s varchar2(20));
- insert into t1 values(1, 1, '1');
- insert into t1 values(2, 2, '2');
- insert into t1 values(3, 3, '3');
- insert into t1 values(NULL, 1, 'NULL.a.1');
- insert into t1 values(NULL, 2, 'NULL.a.2');
- insert into t1 values(NULL, 3, 'NULL.a.3');
- insert into t1 values(NULL, NULL, 'NULL.both.1');
- insert into t1 values(NULL, NULL, 'NULL.both.2');
- insert into t2 values(1, 1, '1');
- insert into t2 values(2, 2, '2');
- insert into t2 values(NULL, 1, 'NULL.a.1');
- insert into t2 values(NULL, 2, 'NULL.a.2');
- insert into t2 values(NULL, NULL, 'NULL.both.1');
- insert into t2 values(NULL, NULL, 'NULL.both.2');
- SQL> select * from t1 join t2 on NVL(t1.a, -1) = NVL(t2.a, -1) and NVL(t1.b, -1) = NVL(t2.b, -1);
- A B S A B S
- ---------- ---------- -------------------- ---------- ---------- --------------------
- 1 1 1 1 1 1
- 2 2 2 2 2 2
- 1 NULL.a.1 1 NULL.a.1
- 2 NULL.a.2 2 NULL.a.2
- NULL.both.1 NULL.both.2
- NULL.both.1 NULL.both.1
- NULL.both.2 NULL.both.2
- NULL.both.2 NULL.both.1
- 8 rows selected.
Add Comment
Please, Sign In to add comment