Hi Tom,
I've always understood it to be the case that a=b and a!=b both evaluate to FALSE if one or both is NULL. However, I'm seeing some odd behavior which I'm trying to distill and understand.
DECLARE
v_Var1 VARCHAR2(12) := 'A';
v_Var2 VARCHAR2(12) := NULL;
BEGIN
--WORKS WHEN BOTH ARE NULL OR BOTH ARE NON-NULL AND EQUAL
IF (v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL)) THEN
DBMS_OUTPUT.PUT_LINE('Same');
END IF;
--SAME TEST AS ABOVE, wrapped in NOT
IF NOT((v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL))) THEN
DBMS_OUTPUT.PUT_LINE('Different');
END IF;
--WORKS, BUT I DON'T KNOW WHY
IF NOT((NVL(v_Var1 = v_Var2,false) OR (v_Var1 IS NULL AND v_Var2 IS NULL))) THEN
DBMS_OUTPUT.PUT_LINE('Different (if NULL=NULL is NULL)');
END IF;
END;
Playing around with v_Var1 and v_Var2, when they're both NULL, or both non-NULL and equal, the first test prints 'Same' (which makes perfect sense, and is a textbook example).
When they differ (specifically when one is NULL and one isn't), I would have expected that the second test would print 'Different', but it doesn't. The logic is intended to be NOT <the case I used to test whether they are the same>.
So I thought to myself, "Maybe v_Var1 = v_Var2 doesn't really resolve to FALSE when one is NULL - maybe it resolves to NULL, and NULL with any operation (e.g. OR, NOT) yields NULL" - and so I tried the third one, which surprisingly worked. But if it was true that it evaluates to NULL, I'd expect that when both variables were NULL, the first test would have evaluated as (NULL or (TRUE and TRUE)), which I'd have expected to result in NULL, and therefore be treated as FALSE. So more likely, I just lucked into something that works for a reason I don't understand.
All that said -
1) why doesn't the first 'Different' test work?
2) what should I have used instead?
3) what's actually happening in the third test?
Thanks!
Easily explained by breaking it down, and printing some results as we go:
SQL> set serverout on
SQL> DECLARE
2 v_Var1 VARCHAR2(12) := 'A';
3 v_Var2 VARCHAR2(12) := NULL;
4
5 b boolean;
6
7 procedure print_bool(p boolean) is
8 begin
9 if p is null then
10 dbms_output.put_line('null');
11 elsif p then
12 dbms_output.put_line('true');
13 else
14 dbms_output.put_line('false');
15 end if;
16 end;
17 BEGIN
18 b := (v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL)) ;
19 print_bool(b);
20
21 b := NOT((v_Var1 = v_Var2 OR (v_Var1 IS NULL AND v_Var2 IS NULL))) ;
22 print_bool(b);
23
24 b := NOT((NVL(v_Var1 = v_Var2,false) OR (v_Var1 IS NULL AND v_Var2 IS NULL))) ;
25 print_bool(b);
26 END;
27 /
null
null
true
PL/SQL procedure successfully completed.
So lets break down test #1
SQL> set serverout on
SQL> DECLARE
2 v_Var1 VARCHAR2(12) := 'A';
3 v_Var2 VARCHAR2(12) := NULL;
4
5 b boolean;
6
7 procedure print_bool(p boolean) is
8 begin
9 if p is null then
10 dbms_output.put_line('null');
11 elsif p then
12 dbms_output.put_line('true');
13 else
14 dbms_output.put_line('false');
15 end if;
16 end;
17 BEGIN
18
19 --
20 -- Components of test #1
21 --
22 b := v_Var1 = v_Var2;
23 print_bool(b);
24 b := (v_Var1 IS NULL AND v_Var2 IS NULL);
25 print_bool(b);
26
27 END;
28 /
null
false
PL/SQL procedure successfully completed.
The "sides" are null and false. And "null OR false" is null.
This is why the NVL works and the others do not.