NOT IN, NOT EXISTS and MINUS: an aide-memoir

A colleague asked me whether NOT IN would return the same as MINUS. I said it would depend on whether the results contained nulls. I confess to not being clear as to how the results would be affected by the presence of nulls, but it's easy enough to knock up a test case.

We start with both tables containing nulls:

SQL> select id from a10
2 /
ID
----------
1

3

3 rows selected.

SQL> select id from a20
2 /
ID
----------
3
2

4

4 rows selected.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /

no rows selected

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1


2 rows selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1

1 row selected.

With a null in the top table but not in the bottom table:

SQL> delete from a20 where id is null
2 /

1 row deleted.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1


2 rows selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1


2 rows selected.

SQL>

With a null in the bottom table but not in the top table:


SQL> delete from a10 where id is null
2 /

1 row deleted.

SQL> insert into a20 values (null)
2 /

1 row created.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /

no rows selected

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1

1 row selected.

SQL>

With no nulls in either table:

SQL> delete from a20 where id is null
2 /

1 row deleted.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1

1 row selected.

SQL>


What's worse than finding a NULL in a NUMBER column?

Finding half a worm! Oops, wrong punchline. No, the worst thing is finding a zero. Twice in the last week I have had to rewrite some code to handle an application which was using zero as a magic value instead of populating a numeric column with null.

One of these columns was a code which described the nature of some free text held in another column. 1 through 6 were assigned to pre-determined categories but the users can enter text outside of those categories. My program did a lookup to expand those codes with the category description; what I hadn't anticipated was that the rows for extra-mural text would have a code of 0 instead of null. NO_DATA_FOUND. Irritating but simple enough to handle.

The other case was slightly more worrying. Here the column was one of two; the other column had a data type of date. One or the other column or neither could be populated, but not both. Overwhelmingly the date column was populated . But when the date column was null roughly have the number columns were zero. Given the nature of the data it was not credible that half those rows would really have a value of zero. Most of them should have been null. However, for a handful of those records zero was probably a legitimate value. It was just impossible to tell which rows they were.

And that is the problem with "handling" nulls by using magic values instead. The magic values often need special handling themselves. Also they can subvert our data integrity. For instance substituting a null with zero may not affect the calculation of a total but will it will change the value of an average.

Nulls are annoying and it is a good idea to avoid them in a data model. But few of us are prepared to go to 6NF. Thus we have columns for which we cannot assign values. So be it. Much better to face the fact and use nulls than to fill our columns with magic values. In a very real sense disguising nulls in such a fashion is just like having an exception handler to suppress exceptions:

    ...
exception
    when no_data_found then
        null;
    ....

We need to know.

Yet Another NULL Article

One of the things that seems to puzzle many Oracle newbies who have experience of other programming languages is that Oracle treats a zero length string as NULL. Over the Christmas period the OTN PL/SQL forum featured an entertaining but ultimately futile discussion on the philosophical ramifications of this behaviour. Personally I cannot see why there should be any more meaning in an empty string than in a NULL but many people think otherwise. And if you deeply believe that there is a meaningful distinction between an empty string and NULL then Oracle's indifference must be galling, if not heretical.

Interestingly, some of the people in this camp might be Oracle engineers. As Laurent Schneider has recently pointed out, Oracle have implemented different behaviour for LOBs. A zero length CLOB is not NULL. We could infer from this that, given the opportunity, Oracle might choose to treat empty VARCHAR2 variables this way too. Then again, it may just be an artefact of the LOB implementation. Either way. the upshot is that Oracle is unlikely to change the existing behaviour simply because such a change would probably break too many existing applications.

Of course, every languages have their quirks, especially with something as slippery as NULL. And developers in other programming languages do funny things with NULL, as this posting from the Daily WTF demonstrates.