UNION query with CodeIgniter's query builder pattern

UNION query with CodeIgniter's query builder pattern

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

Stefan Bogdanescu

Founder & Senior Architect

UNION Queries with CodeIgniter's Query Builder

Executing a UNION operation in CodeIgniter requires understanding how the framework's Active Record pattern handles multiple distinct query segments. Unlike raw SQL, where you simply concatenate strings and manage binding parameters manually, CodeIgniter expects each part of the union to be constructed as a valid, independent database call before combining them.

The most straightforward approach involves chaining the union() method on the result object returned by your first query builder instance. When you invoke $this->db->query(), it returns an object that supports further operations. You can immediately call union() on this instance to append a second query string or, more safely, another Query Builder instance.

Consider a scenario where you need to merge data from two different tables with different column structures. If your first table, users, has columns for id, name, and email, and your second table, admins, has user_id, full_name, and contact_email, a naive SQL concatenation might look like this:

SELECT id, name, email FROM users WHERE active = 1
UNION ALL
SELECT user_id, full_name, contact_email FROM admins WHERE status = 'verified'

In CodeIgniter, you must ensure that the column names match exactly in both parts of the union if you intend to select specific aliases later, although strictly speaking, UNION requires matching types and counts. The framework does not automatically alias columns for you unless you explicitly define them within the SELECT clause of each builder segment.

Here is how you structure this using the Query Builder:

$query1 = $this->db->select('u.id, u.name, u.email')
    ->from('users as u')
    ->where('u.active', 1);

$query2 = $this->db->select('a.user_id, a.full_name, a.contact_email')
    ->from('admins as a')
    ->where('a.status', 'verified');

// Combine the queries
$result = $query1->union($query2);

// Execute and fetch results
$data = $result->getResult();

The critical detail here is that union() returns a new query object. You do not execute the first part, discard it, and then try to build the second part separately in memory; you must pass the configured Query Builder instance into the union method. This ensures that any aliases defined in the SELECT statement are preserved correctly for the combined result set.

One significant pitfall is forgetting to specify the limit or offset after the union operation. If you apply a limit() call directly to one of the builder instances before passing it to union, that limit applies only to that specific segment, not the final combined result. To paginate a UNION query, you must chain the limit() method to the resulting union object:

$paginatedUnion = $result->limit(10);
$data = $paginatedUnion->getResult();

Another common mistake involves using UNION ALL versus UNION. The default behavior of many drivers when you call union() without arguments depends on the database engine and version. However, explicitly choosing union_all() is often preferable for performance if duplicate rows are not a concern, as it skips the sorting step required to eliminate duplicates in a standard UNION.

When dealing with complex joins before the union, remember that you cannot simply join tables across the union boundary. Each side of the union must be self-contained regarding its own joins. If you need data from a third table, say roles, it must be joined within the individual query segments or handled via subqueries if the schema demands it.

For advanced scenarios involving nested unions or mixing UNION with complex aggregations, sticking to raw SQL might sometimes offer clearer control over execution plans and error handling, though CodeIgniter's builder remains robust for standard multi-table merges as long as the structure adheres to the framework's chaining expectations.

Our network