SQL Not Null, Null Mix-up — Lessons for Writing Business Rules

on April 25, 2024
SQL not null

SQL — Is Null, or Is Not Null: That is the Question

Introduced in the 70’s, Structured Query Language (SQL) has standardized the way businesses query and manipulate data. Like with any language, different organizations and vendors will implement SQL slightly differently. But generally, an analyst that uses PostgreSQL to generate reports should be able to easily set campaign rules in an SQL-based email campaign tool. That being said, just because your can, doesn’t mean you should.

In SQL, there is a concept called null which indicates a lack of value. For example, let’s say a business has a loyalty program where their customers can have no status, bronze status, silver status, gold status, or platinum status. Their database is structured such that they have a “Loyalty Status” field to indicate a customer’s status. When they have no status, this field is blank. When they have bronze status, this field is “bronze,” and when they have silver status, this field is “silver” and so forth. Querying records where Loyalty Status is null would pull customers that have no status. Conversely, querying records where Loyalty Status is not null would pull all customers with status, regardless whether they’re bronze, silver, gold, or platinum.

When Null Becomes a Million Dollar Problem

When generating reports, using null can be handy as it’s much easier to code “is null” or “is not null” than having to specify the specific conditions (bronze, silver, gold, platinum). However, creating an email campaign is another matter. A financial services company that we had worked with in the past needed to send out a collections notification to a subset of their customers. At the time, the company had hard-coded and automated their operational emails. Since this was an ad hoc notification, the analyst in charge decided to leverage the email campaign tool that the Marketing team was using. The tool was already integrated with their customer database, so all the analyst would need to do is upload the email content and create a filter to identify the appropriate email contacts.

Since the tool was SQL-based, the analyst thought that this task would be straightforward. To simplify the filter logic, he leveraged null. All customers that he wanted to email would have a past due amount. Therefore, if he created a filter based off records where the past due amount is not null, he should pull the appropriate contacts. Except that’s not what he actually did. Instead of “is not null,” he used “is null.” By forgetting “not,” he ended up emailing everyone except for the customers he intended to email. Yikes! Let’s just say the call center was in for a huge surprise.

Understandability is More Important Than Being Concise

The problem with null is that it’s not intuitive, even for seasoned SQL query writers like that analyst. In an effort to be concise, he sacrificed understandability. Of course the analyst should have at least spot-checked the list before sending out the email. Regardless, if instead he used “greater than 0,” his logic would make more intuitive sense. Plus it’d be less likely that he would confuse “greater than” with “less than.” Whether you’re writing business rules for an email campaign or any automated decision, aim to make them simple and understandable. Your future self will thank you for it.

Want to learn more about how Sparkling Logic is supporting business analysts in simplifying rules authoring?

We cover more tips for business analysts in our post “How to Write Business Rules.”

Also, learn how we’re leveraging generative AI with AI Assistant to make decision management tasks even easier.

Search Posts by Category

ABOUT US

Sparkling Logic Inc. is a Silicon Valley-based company dedicated to helping organizations automate and optimize key decisions in daily business operations and customer interactions in a low-code, no-code environment. Our core product, SMARTS™ Data-Powered Decision Manager, is an all-in-one decision management platform designed for business analysts to quickly automate and continuously optimize complex operational decisions. Learn more by requesting a live demo or free trial today.