Passionate about data

Data and its implications on software design and development.

Nulls Need Special Love

The following SELECT statement in code

1
2
3
4
5
6
7
8
stmt = DB.prepare("select id,name,state,zip " +
        "from customer " +
        "where " +
        "phone = ? " +
        "and active = ?");
stmt.setString(1, customerPhone);
stmt.setBoolean(2, isActive);
stmt.execute();

where customerPhone and isActive are values you would pass in to the SELECT before its executed. Everything is fine when one day the value passed for customerPhone is NULL. For a database (Oracle is what I know most) a NULL will never be equal to NULL , the SELECT will not return rows where the customer.phone is NULL, leading to wrong results. The SELECT will have to be changed to

1
2
3
4
5
6
7
8
9
stmt = DB.prepare("select id,name,state,zip " +
        "from customer " +
        "where " +
        "(phone IS NULL or phone = ?) " +
        "and " +
        "active = ?");
stmt.setString(1, customerPhone);
stmt.setBoolean(2, isActive);
stmt.execute();

We could dynamically write the SELECT so that we don’t have to do the OR in the where clause, which could be expensive.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
boolean hasPhone = false;
StringBuffer sqlQuery = new StringBuffer();
sqlQuery.append("select id,name,state,zip " +
                "from customer " +
                "where 1=1 ");
if (phoneNumber != null) {
    sqlQuery.append("and phone=? ");
    hasPhone = true;
} else {
    sqlQuery.append("and phone IS NULL ");
}
sqlQuery.append("and isActive=?");
stmt = DB.prepare(sqlQuery.toString());
if (hasPhone) {
    stmt.setString(1, phoneNumber);
    stmt.setBoolean(2, isActive);
} else {
    stmt.setBoolean(1, isActive);
}
stmt.execute();