CODEWITHSUNDEEP
sqlplsqloracle11g
Brian Dishaw
Brian Dishaw

Reputation: 5825

Why does null <> "something" return false

I was debugging a procedure in an oracle database when I came across something that surprised me regarding NULL values. Can anybody explain why the following query returns false for the non equality check here?

DECLARE
    vNullVariable VARCHAR2(2) := NULL;
    vVariable VARCHAR2(2) := 'Hi';
BEGIN
    IF vNullVariable <> vVariable THEN
        dbms_output.put_line( 'The variables are not equal' );
    ELSE
        dbms_output.put_line( 'The variables are equal' );
    END IF;
END;

Upvotes: 1

Views: 1810

Answers (3)

Nick Krasnov
Nick Krasnov

Reputation: 27261

In Oracle, NULL is neither equal to nor not equal to NULL. A comparison condition that evaluates two NULLs will always be FALSE. The following conditions involving NULL will be valid.

1. IF vNullVariable is NULL THEN
2. IF vNullVariable is not NULL THEN
3. IF NVL(vNullVariable, -1) <> NVL(vVariable, -1) THEN -
    This condition will give you expected result even if both vNullVariabl 
    and vVariable are NULLs
4. Oracle considers two nulls to be equal when evaluating a DECODE function

for example the following query will give you 1 if first and second arguments of DECODE function are NULLs

    select decode(null, null, 1) res
      from dual;

 res
-------
  1

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132680

This is because SQL uses three-valued logic (3VL): there is TRUE, there is FALSE and there is NULL (unknown, neither TRUE nor FALSE).

The result of the expression vNullVariable <> vVariable is NULL, not TRUE, in 3VL because it considers the value of vNullVariable to be unknown: if at a later time it becomes a known value, it might be 'Hi' or it might not, but right now SQL doesn't know so it returns NULL (unknown).

So the IF expression evaluates to NULL, not TRUE, and so the default ELSE path is taken instead - because the logic if IF is:

IF <expression is true> THEN
   ...
ELSE -- ie. if expression is FALSE or NULL
   ...
END IF;

This means that you would get the behaviour you were expecting if you wrote the check the other way around:

IF vNullVariable = vVariable THEN
    dbms_output.put_line( 'The variables are equal' );
ELSE
    dbms_output.put_line( 'The variables are not equal' );
END IF;

Upvotes: 8

Tux9R
Tux9R

Reputation: 37

NULL's has strange behaviour on Oracle. To compare something with null, is better to use IS NULL instead of '=NULL'

Here you have a good explanation about

In your code, you can use

IF vVariable IS NOT NULL THEN

Upvotes: 2

Related Questions