UNION Operations

Combine results from multiple queries

UNION Operations#

Combine results from multiple queries using UNION and UNION ALL.

Basic UNION#

Combines results and removes duplicates:

final db = KnexQuery.forDialect(KnexDialect.postgres);

final q1 = db.from('users').select(['name']).where('active', '=', true);
final q = q1.union([
  db.from('users').select(['name']).where('role', '=', 'admin')
]).toSQL();
print(q.sql);
// select "name" from "users" where "active" = $1
// union
// select "name" from "users" where "role" = $2

Try in Playground ↗

Or chain directly:

final q = db.from('users')
    .select(['name'])
    .where('active', '=', true)
    .union([
      db.from('users').select(['name']).where('role', '=', 'admin')
    ])
    .toSQL();
print(q.sql);

Try in Playground ↗

UNION ALL#

Keeps all rows including duplicates:

final q = db.from('users')
    .select(['name'])
    .where('type', '=', 'customer')
    .unionAll([
      db.from('users').select(['name']).where('type', '=', 'admin')
    ])
    .toSQL();
print(q.sql);
// select "name" from "users" where "type" = $1
// union all
// select "name" from "users" where "type" = $2

Try in Playground ↗

Multiple UNIONs#

Combine more than two queries:

final q = db.from('users')
    .where('type', '=', 'customer')
    .union([
      db.from('users').where('type', '=', 'admin'),
      db.from('users').where('type', '=', 'moderator')
    ])
    .toSQL();
print(q.sql);
// select * from "users" where "type" = $1
// union select * from "users" where "type" = $2
// union select * from "users" where "type" = $3

Try in Playground ↗

UNION with ORDER BY/LIMIT#

Apply ordering and limiting to final result:

final q = db.from('users')
    .where('active', '=', true)
    .union([
      db.from('users').where('role', '=', 'admin')
    ])
    .orderBy('name')
    .limit(10)
    .toSQL();
print(q.sql);
// select * from "users" where "active" = $1
// union select * from "users" where "role" = $2
// order by "name" asc limit $3

Try in Playground ↗

Column Alignment#

All queries must have same number and type of columns:

// ✅ Correct - same columns
final q = db.from('users').select(['id', 'name'])
    .union([
      db.from('admins').select(['id', 'name'])
    ])
    .toSQL();
print(q.sql);

// ❌ Error - different columns
final q2 = db.from('users').select(['id', 'name'])
    .union([
      db.from('admins').select(['id'])  // Column mismatch!
    ])
    .toSQL();
print(q2.sql);

Try in Playground ↗

UNION vs UNION ALL#

Feature	UNION	UNION ALL
Duplicates	Removed	Kept
Performance	Slower (deduplication)	Faster
Use when	Need unique results	All rows needed

// UNION - removes duplicate names
final q = db.from('customers').select(['name'])
    .union([
      db.from('employees').select(['name'])
    ])
    .toSQL();
print(q.sql);
// Result: ['John', 'Jane', 'Bob'] (unique)

// UNION ALL - keeps all names
final q2 = db.from('customers').select(['name'])
    .unionAll([
      db.from('employees').select(['name'])
    ])
    .toSQL();
print(q2.sql);
// Result: ['John', 'Jane', 'Bob', 'John'] (with duplicates)

Try in Playground ↗

Complex Example#

// Get all active users from different sources
final regularUsers = db.from('users')
    .select(['id', 'email', db.queryBuilder().client.raw("'regular' as type")])
    .where('active', '=', true);

final adminUsers = db.from('admins')
    .select(['id', 'email', db.queryBuilder().client.raw("'admin' as type")])
    .where('active', '=', true);

final guestUsers = db.from('guests')
    .select(['id', 'email', db.queryBuilder().client.raw("'guest' as type")])
    .where('session_active', '=', true);

final q = regularUsers
    .unionAll([adminUsers, guestUsers])
    .orderBy('type')
    .orderBy('email')
    .toSQL();
print(q.sql);

Try in Playground ↗

UNION with CTEs#

Combine UNION with CTEs for complex queries:

final q = db.queryBuilder()
    .withQuery('all_users',
      db.from('customers').select(['id', 'name'])
        .union([
          db.from('employees').select(['id', 'name'])
        ])
    )
    .from('all_users')
    .select(['*'])
    .where('name', 'like', 'J%')
    .toSQL();
print(q.sql);

Try in Playground ↗

Parameter Handling#

Knex Dart automatically:

✅ Renumbers parameters across UNIONs
✅ Merges bindings correctly
✅ Maintains parameter sequence

final q = db.from('users')
    .where('active', '=', true)   // $1
    .union([
      db.from('users').where('role', '=', 'admin')  // $2 (not $1!)
    ])
    .limit(10)  // $3
    .toSQL();
print(q.sql);
// Bindings: [true, 'admin', 10]

Try in Playground ↗

Best Practices#

Match column count - All UNIONed queries must have same columns
Use UNION ALL when possible - Faster if duplicates don't matter
Apply ORDER BY/LIMIT at the end - On final result, not individual queries
Name columns consistently - Use aliases for clarity

// Good
final q = db.from('table1').select(['id', 'name as full_name'])
    .union([
      db.from('table2').select(['id', 'username as full_name'])
    ])
    .toSQL();
print(q.sql);

// Bad - inconsistent naming
final q2 = db.from('table1').select(['id', 'name'])
    .union([
      db.from('table2').select(['id', 'username'])
    ])
    .toSQL();
print(q2.sql);

Try in Playground ↗

Next Steps#

CTEs - WITH clauses
Subqueries - Nested queries
Examples - Real-world patterns