SQL Snippets ™: Tutorials for Oracle Developers

Read Using SQL Snippets ™ before using any of this site's sample code or techniques on your own systems.

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

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.

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.