The Dev World – Sergio Lema

Few days ago I had to write a SQL query to obtain all the lines but those with a single value.

This seems easy, right?

Here is my table:

create table book (
   id serial,
   title text not null,
   topic text not null,
   sub_topic text
);

And here is my query:

SELECT COUNT(1) 
  FROM book 
  WHERE sub_topic != 'HORROR';

If first try my query on the database. It works. So I put it on my ORM and push to production.

BUT!

Let’s examine this.

The Problem

First of all, null is always a bad choice. As said by Tony Hoare, the null value is the billion-dollar mistake. Try to avoid having null everywhere. In the database and in the code.

Let’s go now to the database and see the different values of the column sub_topic.

SELECT sub_topic, COUNT(1)
  FROM book
  GROUP BY sub_topic;

 sub_topic   | count
-------------+-------
 HORROR      | 15
 LOVE        | 5
 ECONOMICS   | 20
 HISTORY     | 35
 PROGRAMMING | 104
 MISTERY     | 23
             | 203

I can see in the database the different values and the number of rows with each value.

Let’s see now the result of my initial query.

SELECT COUNT(1) 
  FROM book 
  WHERE sub_topic != 'HORROR';

 count
-------
 187

There are missing rows. When I said I want all the books with a sub topic different that Horror, I also want the books with no sub topic.

The Solution

I have to take into account that a query filtering the null values is quite different.

SELECT COUNT(1) 
  FROM book 
  WHERE sub_topic IS NULL;

 count
-------
 203

Instead of using the operator = or !=, I have to use the operator IS NULL or IS NOT NULL.

So, when fetching all the rows with values different than Horror, the rows with null values must be fetch in another way.

SELECT COUNT(1) 
  FROM book 
  WHERE sub_topic != 'HORROR'
    OR sub_topic IS NULL;

 count
-------
 390

Conclusion

First of all, I would try to not use the null value anymore. Because I have to remember to adapt my query to the null values. And sooner or later, I will skip it.

But if I really need to use null values in a column, I must adapt all my queries as before.

It’s now I see that this is really a billion-dollar mistake.