SELECT query WHERE NOT IN a subquery using CodeIgniter's Query Builder methods

SELECT query WHERE NOT IN a subquery using CodeIgniter's Query Builder methods

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

Stefan Bogdanescu

Founder & Senior Architect

Handling NOT IN Subqueries in CodeIgniter Query Builder

Translating raw SQL into an Active Record framework like CodeIgniter often requires more than simple syntactic translation; it demands understanding how the underlying method chains interact with complex logical structures. When you need to filter records where a column value does not exist in a subquery result set—specifically SELECT * FROM certs WHERE id NOT IN (SELECT id_cer FROM revokace)—the standard approach using where_in() fails immediately. The where_in() method constructs an IN (...) clause, which is the logical opposite of what your query requires. Attempting to use it directly will return results that are already revoked, effectively inverting your entire security or logic model.

To achieve the NOT IN condition, you must construct the subquery separately and then pass its representation to the main query builder. The core mechanism relies on CodeIgniter's ability to handle nested queries through the having() method when combined with a custom expression, but for a direct WHERE NOT IN, the most robust pattern involves using the where_not_in() helper or constructing a raw subquery string if the framework version does not support passing a query object directly in that specific context.

In modern CodeIgniter 4 environments, the Query Builder provides a dedicated method to handle this exact scenario without falling back to raw SQL strings unnecessarily. You can construct the subquery first using $db->query() or by chaining methods onto a temporary builder instance, and then pass that entire object into the where_not_in() method.

Here is how you would implement this in a controller or model:

use CodeIgniter\Database\Database;

public function getActiveCerts()
{
    $db = \Config\Database::connect();

    // 1. Construct the subquery to find revoked certificate IDs
    // We select id_cer from revokace table
    $subQuery = $db->select('id_cer')
        ->from('revokace');

    // 2. Execute the subquery or keep it as an object depending on CI version
    // In many CI contexts, we can pass the query builder object directly to where_not_in
    
    $this->builder = $db->table('certs')
        ->select('*')
        ->whereNotIn('id', $subQuery);

    return $this->builder->get()->getResultArray();
}

The critical detail here is the use of whereNotIn(). Unlike standard SQL where you might write WHERE id NOT IN (SELECT ...) directly, CodeIgniter's Query Builder expects the second argument of this method to be either a specific set of values or a subquery object. When you pass the $subQuery object constructed in step 1, the framework recognizes it as a nested query and automatically wraps it in parentheses within the generated SQL string, resulting in the correct NOT IN clause.

However, developers should remain vigilant about edge cases involving NULL values. If the subquery revokace table contains any rows where id_cer is NULL, the standard SQL interpretation of NOT IN behaves unexpectedly. In SQL logic, if a list includes a NULL value, the entire NOT IN condition evaluates to UNKNOWN for every row, effectively returning zero results. This is because x != NULL is not true; it is unknown.

To mitigate this risk without resorting to raw SQL immediately, you should ensure your subquery explicitly handles nulls or filter them out:

$subQuery = $db->select('id_cer')
    ->from('revokace')
    ->whereNull('id_cer', false); // Exclude NULLs from the subquery result

// Then pass this to whereNotIn
$this->builder->whereNotIn('id', $subQuery);

By excluding NULL values from the subquery, you ensure that the NOT IN operator functions as intended, filtering out only those IDs that are explicitly present in the revocation list. This approach maintains the safety of using Query Builder while respecting the strict logical rules of SQL. If your CodeIgniter version or specific database driver lacks support for passing query objects directly into whereNotIn(), you may need to switch to raw SQL injection via the builder's rawWhere() method, but always parameterize any dynamic parts to prevent SQL injection vulnerabilities.

For more complex scenarios involving multiple tables or joins before applying this filter, remember that the subquery must be fully resolved before the main join logic is applied in the generated execution plan. Always test the raw SQL output using the $db->getLastQuery() method during development to verify the structure matches your expectations.

Our network