Value IS NOT NULL in codeigniter

Value IS NOT NULL in codeigniter

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Value IS NOT NULL in CodeIgniter

When you write a raw SQL query like SELECT * FROM donors WHERE field IS NOT NULL, you are explicitly asking the database to return rows where the specified column contains any value, including an empty string or zero, but excluding actual NULL states. The intent is clear: filter out missing data. However, translating this logic directly into CodeIgniter's Query Builder using $where = ['field' => NULL] often results in a query that returns nothing, or worse, one that behaves exactly opposite to your intent due to how the framework handles null comparisons internally.

The root of the confusion lies in the distinction between an empty value and a null reference within PHP arrays passed to database methods. When you assign NULL to a key in your $where array, CodeIgniter's active record layer interprets this as a request for rows where that field is NULL. It generates SQL equivalent to WHERE field IS NULL. This is a common pitfall when migrating from raw query thinking to framework abstraction. The framework treats the PHP NULL value in the criteria array as a boolean flag indicating "search for nulls," not "exclude nulls."

To achieve the IS NOT NULL behavior, you must explicitly tell the database engine that the field should not be missing. There are three primary ways to handle this within CodeIgniter, each with specific trade-offs depending on your version and whether you need case sensitivity or strict type checking.

Using Explicit Comparison Operators

The most robust method is to bypass the automatic array-to-SQL conversion for null checks and inject a comparison operator directly. You can use the where() method with a custom expression:

$this->db->where("field IS NOT NULL", NULL, FALSE);
$query = $this->db->get('table');

Here, the third argument of where(), $escape, is set to FALSE because we are passing raw SQL logic. This forces CodeIgniter to append AND (field IS NOT NULL) to your query string. Alternatively, you can simply pass a non-null value like an empty string if your database driver treats it as a "truthy" filter that excludes the null state in specific versions, but relying on implicit behavior is risky. Explicitly writing the SQL clause ensures portability across MySQL, PostgreSQL, and SQLite without worrying about version-specific quirks regarding how NULL is handled in comparison operators.

Using Raw Queries for Complex Logic

If you find yourself dealing with complex aggregation or subqueries where the null check logic becomes tangled with other conditions, switching to a raw query might be cleaner:

$this->db->select('*');
$this->db->from('table');
$this->db->where('field IS NOT NULL');
$query = $this->db->get();

This approach gives you full control over the generated SQL. While it sacrifices some of the helper methods available on the query object, it guarantees that the IS NOT NULL syntax is preserved exactly as intended by your database engine.

Understanding the Trade-Offs

Relying on the array assignment method ($where = ['field' => NULL]) is convenient but dangerous when you intend to exclude nulls. It introduces a cognitive gap where developers assume "no value" means "filter out missing data," while the framework assumes it means "find the missing data." This discrepancy can lead to silent bugs in production where records are inadvertently dropped from reports because they were actually NULL, or conversely, records appear when you expected them to be filtered out.

When debugging these issues, always inspect the actual SQL generated by logging the query builder state before execution. In modern CodeIgniter versions, the framework has improved error handling for invalid queries, but understanding the underlying mechanics remains essential for writing performant and correct data access logic. If you are maintaining legacy codebases where this pattern is common, refactoring to explicit where() calls with raw expressions is a high-priority task to ensure consistency across your application's data retrieval strategies.

Our network