> I probably haven't considered all the possible cases because I rarely > use IS NULL. When dealing with dynamic SQL, the code behind a typical > "search window" is more like : > > if( StringUtils.isBotBlank( field.getText() ) ) > //add condition on column > else > //do nothing (no null logic)
That's the simple SQL uses. However, more advanced uses tend to turn that perspective into an unavailable luxury. Once you start writing outer joins, null handling becomes really important. For example, to test whether a relationship exists, you can test any primary key column: if it's null, the corresponding related record didn't exist. This isn't important when doing interactive SQL but quite common in batch processing, where you try to shove as much work as possible into the query optimizer. The other usage is where a null value has a real semantics, different from that of an empty string or a zero value. In fact, I have several fields where zero means "upper limit is zero, i.e. don't allow anything", and null is "no upper limit, i.e. allow anything" (quantities to purchase, in this case). I even built a JTextField descendant that clearly differentiates between the two, and shows a different representation for a null value if it doesn't have focus. For strings, the difference between null and an empty string isn't so easy to represent and in fact rarely if ever needed. In those cases where I do have a nullable string fields, it's always been an enumeration, and these I'm converting between DB representation and screen representation anyway.
