Nulls and Equality: SQL Only

The following techniques, while more compact than the solutions presented in SQL + PL/SQL , unfortunately only work in SQL commands, not PL/SQL commands.

DECODE

One approach uses the DECODE function. Unlike the "=" operator, DECODE treats two nulls as equivalent. To return rows where two columns contain the same value we can therefore use a command like the following.

select *
from   t
where  DECODE( C2, C3, 'Y', 'N' ) = 'Y' ;

 C1 C2         C3
--- ---------- ----------
  1 A          A
  4 (null)     (null)

DUMP

Another approach uses the DUMP function.

select *
from   t
where  DUMP(C2) = DUMP(C3) ;

 C1 C2         C3
--- ---------- ----------
  1 A          A
  4 (null)     (null)

This approach has a couple of limitations however. One, the output of the DUMP function is truncated at 4000 characters. If the values being compared produce truncated DUMP output then the comparison can produce false positives. Here is an example.

select 'Oops! This row should not be returned.' as result
from   dual
where  DUMP( LPAD( 'A', 4000) ) = DUMP( LPAD( 'B', 4000 ) ) ;

RESULT
--------------------------------------
Oops! This row should not be returned.

Two, C2 and C3 must be the exact same datatype for the comparison to work. Comparing compatible datatypes such as VARCHAR2 and CHAR will fail to match any rows.

select 'Oops! This row should be returned, but it is not.' as result
from   t
where  dump( c2 ) = dump( 'A' ) ;

no rows selected

Examining the output of DUMP shows why this occurs. The "typ=" part of the DUMP output for both terms differs because column C2 is datatype 1, VARCHAR2, and the literal 'A' is datatype 96, CHAR.

column varchar2_val format a30
column char_val     format a30 fold_before

select
  dump( c2 )  as varchar2_val ,
  dump( 'A' ) as char_val
from
  t
where
  c1 = 1
;

VARCHAR2_VAL
------------------------------
CHAR_VAL
------------------------------
Typ=1 Len=1: 65
Typ=96 Len=1: 65

SYS_OP_MAP_NONNULL

Another approach that some have proposed uses the undocumented function SYS_OP_MAP_NONNULL.

select *
from   t
where  SYS_OP_MAP_NONNULL( C2 ) = SYS_OP_MAP_NONNULL( C3 ) ;

 C1 C2         C3
--- ---------- ----------
  1 A          A
  4 (null)     (null)

As with the other solutions on this page, SYS_OP_MAP_NONNULL does not work in PL/SQL.

begin

  for r in ( select * from t )
  loop

    if ( SYS_OP_MAP_NONNULL( R.C2 ) = SYS_OP_MAP_NONNULL( R.C3 ) )
    then
      dbms_output.put_line( 'Row ' || r.c1 || ' contains matching values.' );
    end if;

  end loop;

end;
.
/

    if ( SYS_OP_MAP_NONNULL( R.C2 ) = SYS_OP_MAP_NONNULL( R.C3 ) )
         *
ERROR at line 6:
ORA-06550: line 6, column 10:
PLS-00201: identifier 'SYS_OP_MAP_NONNULL' must be declared
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored

It also has a length limitation.

select *
from   dual
where  SYS_OP_MAP_NONNULL( LPAD( 'A', 4000 ) ) = SYS_OP_MAP_NONNULL( LPAD( 'B', 4000 ) ) ;
select *
*
ERROR at line 1:
ORA-01706: user function result value was too large

While undocumented features such as this one are compelling, their behaviour or availability can change at any time making them a risky thing to include in your code. They also make support and maintenance harder for others who need to work with your code and are not familiar with the feature.

SQL Snippets ™: Tutorials for Oracle Developers