SQLite Subquery Performance

SQLite Subquery Performance is a SQL statement in the SQLite Specific category. SQLite: scalar subqueries in SELECT can be slow for large datasets. Use JOIN or window functions instead. The syntax is -- Correlated vs non-correlated subquery performance in SQLite.. It returns performance comparison. A typical example: -- Slow (row-by-row): SELECT name, (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS dept_avg FROM employees e; -- Correlated subquery runs for EACH row -- Fast (single pass): SELECT e.name, d.dept_avg FROM employees e LEFT JOIN ( SELECT dept_id, AVG(salary) AS dept_avg FROM employees GROUP BY dept_id ) d ON e.dept_id = d.dept_id; -- Subquery runs ONCE, then joined -- Use EXPLAIN QUERY PLAN to verify performance A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.