## Nulls and Equality

### SQL + PL/SQL

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.

#### 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-10579.html]SQL Snippets: Nulls and Equality - SQL + PL/SQL [/url]