This article is based on the OTN thread Using <> or != on a NULL

Checking two variables for equality is easy, when they contain values. NULL, however, requires extra work, so does NULL == NULL. One method is using the IS / IS NOT NULL comparison:

IF col1 = col2 OR (col1 IS NULL AND col2 IS NULL)
THEN
  -- here comes the "==" (equal) part
ELSE
  -- here comes the "<>" (not equal) part
END IF;

or a shorter version:

IF col1 = col2 OR NVL(col1, col2) IS NULL

as suggested by Aketi Jyuuzou. However, if you want to check for inequality, a simple NOT won't do, as shown in the following example:

(Tested on an Oracle XE instance which equals to 10g2)

WITH t1 AS (SELECT 1 col
              FROM dual
             UNION
            SELECT NULL
              FROM dual
           )
   , t  AS (SELECT t1.col col1
                 , t2.col col2
              FROM t1
                 , t1 t2
           )
SELECT t.col1
     , t.col2
     , CASE WHEN t.col1 = t.col2 OR NVL(t.col1, t.col2) IS NULL
            THEN '=='
            ELSE '<>'
            END v1
     , CASE WHEN NOT (t.col1 = t.col2 OR NVL(t.col1, t.col2) IS NULL)
            THEN '<>'
            ELSE '=='
            END v2
     , CASE WHEN t.col1 <> t.col2 
              OR (t.col1 IS NULL AND t.col2 IS NOT NULL) 
              OR (t.col1 IS NOT NULL AND t.col2 IS NULL)
            THEN '<>'
            ELSE '=='
            END v3
  FROM t
; 
COL1 COL2 V1 V2 V3
---- ---- -- -- --
   1    1 == == ==
   1      <> == <>
        1 <> == <>
          == == ==

The correct check for inequality is column v3. I'm using this approach for so-called audit triggers, that check for changed column values after an UPDATE of table rows. The SQL engine offers DECODE as an alternative:

(to save space, I removed the WITH statement, please copy that part from the previous statement)

 
SELECT t.col1
     , t.col2
     , DECODE(t.col1, t.col2, '==', '<>') v4
  FROM t
; 
COL1 COL2 V4
---- ---- --
   1    1 ==
   1      <>
        1 <>
          ==

Since DECODE is not available in PL/SQL, Laurent Schneider offerd a PL/SQL only version:

SET SERVEROUTPUT ON
DECLARE
  TYPE t_num_arr IS TABLE OF NUMBER;
  v_arr t_num_arr := t_num_arr(1, NULL);
  v_eq  VARCHAR2(2);
BEGIN
  FOR i IN 1 .. v_arr.COUNT
  LOOP
    FOR j IN 1 .. v_arr.COUNT
    LOOP
      IF NVL(v_arr(i) != v_arr(j), NVL(v_arr(i), v_arr(j)) IS NOT NULL)
      THEN
         v_eq := '<>';
      ELSE
         v_eq := '==';
      END IF;
      
      DBMS_OUTPUT.PUT_LINE(v_arr(i) || v_eq || v_arr(j));
    END LOOP;
  END LOOP;
END;
/
1==1
1<>
<>1
==

Due to that audit trigger concept I mentioned in the beginning, I found another solution, which may look a bit cryptic first (works in SQL and PL/SQL):

SELECT t.col1
     , t.col2
     , CASE WHEN NVL(NULLIF(t.col1, t.col2), NULLIF(t.col2, t.col1)) IS NOT NULL
            THEN '<>'
            ELSE '=='
            END v6
  FROM t
;
COL1 COL2 V6
---- ---- --
   1    1 <>
   1      ==
        1 ==
          <>

NULLIF checks both values and returns NULL, if those values are equal or the first argument. Since that could also be NULL, a second NULLIF where the compared columns are switched, is necessary.

Thanks to another forum member, erich.kitzmueller, there was another, more readable (PL/SQL only) solution:

SET SERVEROUTPUT ON
DECLARE
  TYPE t_num_arr IS TABLE OF NUMBER;
  v_arr t_num_arr := t_num_arr(1, NULL);
  v_eq  VARCHAR2(2);
BEGIN
  FOR i IN 1 .. v_arr.COUNT
  LOOP
    FOR j IN 1 .. v_arr.COUNT
    LOOP
      IF v_arr(i) != v_arr(j) OR (v_arr(i) IS NULL) != (v_arr(j) IS NULL)
      THEN
         v_eq := '<>';
      ELSE
         v_eq := '==';
      END IF;
      
      DBMS_OUTPUT.PUT_LINE(v_arr(i) || v_eq || v_arr(j));
    END LOOP;
  END LOOP;
END;
/
1==1
1<>
<>1
==

Needless to say that I've switched to this new syntax in my audit triggers.

Reference Links

Using <> or != on a NULL