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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-10574.html]SQL Snippets: Nulls and Equality - SQL Only[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-10574.html">SQL Snippets: Nulls and Equality - SQL Only</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Nulls and Equality - SQL Only
  • URL (href): http://www.sqlsnippets.com/en/topic-10574.html

Revision Notes

Date Category Note
2007-12-06 Revision Added explanation re. DUMP and differing datatypes. Thank you to Laurent Schneider for pointing this effect out at OTN Discussion Forum Thread "Using <> or != on a NULL".