too many terms in compound SELECT

This error occurs when you use too many UNION, EXCEPT, or INTERSECT operations in a single query. SQLite limits compound SELECT terms to 500 by default.

The too many terms in compound SELECT error means you've exceeded SQLite's limit for UNION/EXCEPT/INTERSECT operations.

Understanding the Error

Error: too many terms in compound SELECT

Default limit is 500 compound terms.

Common Causes

1. Generating Large UNION Queries

SQL

-- Programmatically building huge UNION
SELECT * FROM table1
UNION SELECT * FROM table2
UNION SELECT * FROM table3
-- ... 500+ more UNIONs

2. Dynamic Query Building Gone Wrong

JAVASCRIPT

// Building a massive UNION query
let sql = 'SELECT 1 AS id';
for (let i = 2; i <= 1000; i++) {
  sql += ` UNION SELECT ${i}`;
}
db.exec(sql);  // Error: too many terms

How to Fix It

Solution 1: Use Temp Table Instead

JAVASCRIPT

// Instead of huge UNION
db.exec('CREATE TEMP TABLE results (id INTEGER)');

for (const value of values) {
  db.run('INSERT INTO results VALUES (?)', [value]);
}

const results = db.prepare('SELECT * FROM results').all();
db.exec('DROP TABLE results');

Solution 2: Batch the Queries

JAVASCRIPT

function batchUnion(db, queries, batchSize = 100) {
  const results = [];

  for (let i = 0; i < queries.length; i += batchSize) {
    const batch = queries.slice(i, i + batchSize);
    const sql = batch.join(' UNION ');
    const batchResults = db.prepare(sql).all();
    results.push(...batchResults);
  }

  return results;
}

Solution 3: Use IN Clause

If just filtering by values:

SQL

-- Instead of:
SELECT * FROM users WHERE id = 1
UNION SELECT * FROM users WHERE id = 2
-- ... hundreds more

-- Use:
SELECT * FROM users WHERE id IN (1, 2, 3, ...);

Solution 4: Use VALUES Clause

SQL

-- Create inline table
SELECT * FROM (
  VALUES (1), (2), (3), (4), (5)
) AS t(id);

Solution 5: Increase Limit (If You Control Build)

// Compile-time option
#define SQLITE_MAX_COMPOUND_SELECT 1000

Best Practices

Avoid huge UNION chains in application code
Use temp tables for large datasets
Use IN clause for simple value lists
Batch operations when necessary
Consider data model - might need different approach