Understanding SQL NOT LIKE and NULL Values
Today I encountered an interesting SQL behavior while troubleshooting a query that unexpectedly returned far fewer records than expected.
The Problem
My query was returning approximately 44,000 records. However, after adding the filter:
AND b.department_category NOT LIKE ''%Requested Deviation%''
the result count suddenly dropped to only 28 records.
Investigation
When I checked the data more closely, I found that most of the records had department_category = NULL.
Understanding the Behavior
Many developers assume that NOT LIKE will include rows with NULL values, but that is not how SQL works. Any comparison involving NULL returns UNKNOWN rather than TRUE or FALSE.
For example:
NULL NOT LIKE ''%Requested Deviation%''
returns UNKNOWN.
Since the WHERE clause only keeps rows that evaluate to TRUE, rows with NULL values are excluded from the result set.
Why I Got Only 28 Records
The majority of the 44,000 records had NULL values in the department_category column.
When I applied the NOT LIKE condition, SQL excluded all those NULL records, leaving only the small set of non-null records that did not match the filter.
The Fix
Use either:
AND (b.department_category IS NULL OR b.department_category NOT LIKE ''%Requested Deviation%'')
or
AND ISNULL(b.department_category,'') NOT LIKE ''%Requested Deviation%''
Key Takeaway
- SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.
- Any comparison involving NULL returns UNKNOWN, and rows evaluating to UNKNOWN are filtered out by the WHERE clause.
Lesson Learned
- When using NOT LIKE, always check for NULL values. A large number of NULLs can significantly reduce the result set and lead to unexpected query results.
- A simple yet powerful reminder that understanding how SQL handles NULL values can save a lot of debugging time.
Comments
Post a Comment