SQL Snippets ™: Tutorials for Oracle Developers

These techniques work in both SQL and PL/SQL.

OR with IS NULL

While a bit cumbersome, this basic solution is the easiest to understand and implement.

select *
from   t
where  ( C2 = C3 OR ( C2 IS NULL AND C3 IS NULL ) );
 

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

begin

  for r in ( select * from t )
  loop

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

  end loop;

end;
.
/
 

Row 1 contains matching values.
Row 4 contains matching values.
 

NVL

The following NVL approach is a popular one.

select *
from   t
where  nvl( c2, 'x' ) = nvl( c3, 'x' ) ;
 

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

One problem with this solution is that the replacement value "x", or whatever value you choose to use, might be inserted into columns C2 or C3 some day. This would cause a SELECT statement that has been working properly until that day to all of a sudden start returning the wrong answer, like this.

insert into t values( 5, 'x', null );
commit;

select *
from   t
where  nvl( c2, 'x' ) = nvl( c3, 'x' ) ;
 

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

The trick to making this solution bullet proof is to choose a replacement value that can never appear in either of the columns being compared. If we look at the table definition for T

desc t
 Name                                           Null?    Type
 ---------------------------------------------- -------- -------------------------------
 C1                                                      NUMBER
 C2                                                      VARCHAR2(10)
 C3                                                      VARCHAR2(10)

 

we see that values in C2 and C3 can be at most 10 characters long. Any replacement value larger than 10 characters is therefore guaranteed to never appear in either column (assuming the sizes of C2 or C3 are never expanded).

select *
from   t
where  nvl( c2, '12345678901' ) = nvl( c3, '12345678901' ) ;
 

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

Custom Function

If you do these comparisons frequently you may wish to create a custom database function like this one.

create function SAME( p_1 in varchar2, p_2 in varchar2 ) return varchar2 is
begin
  return
  ( case
    when p_1 is null and p_2 is null then 'Y'
    when p_1 = p_2 then 'Y'
    else 'N'
    end
  );
end;
/
 

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

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

begin

  for r in ( select * from t )
  loop

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

  end loop;

end;
.
/
 

Row 1 contains matching values.
Row 4 contains matching values.
 

With this approach however one function is required for comparing NUMBER values, one for VARCHAR2 values, one for DATE values, etc.