SQLite中EXECUTE LIST子查询的含义及是否缓存的技术问询

Great question—let's unpack what EXECUTE LIST means in SQLite and whether it gets cached, building on what you already know about EXECUTE SCALAR from the execution plan documentation.

What Does EXECUTE LIST Mean? #

In SQLite's query execution plans, EXECUTE LIST is the marker for running a subquery that returns multiple rows of results. This comes into play whenever your main query relies on a subquery that outputs more than a single value—think scenarios like:

• Using a subquery with IN (SELECT ...)
• Including a subquery as a derived table in the FROM clause
• Using a subquery that feeds multiple values to a join

For example, take this query:

SELECT product_name FROM products WHERE category_id IN (SELECT id FROM categories WHERE is_active = 1);

The subquery SELECT id FROM categories WHERE is_active = 1 returns multiple category IDs, so SQLite will use EXECUTE LIST to handle executing this multi-row subquery and feeding its results to the main query.

Does EXECUTE LIST Get Cached? #

Here's where it differs from EXECUTE SCALAR: by default, SQLite does NOT cache the results of an EXECUTE LIST subquery.

EXECUTE SCALAR caches single-value results because they're small and reusing them is straightforward. But EXECUTE LIST deals with multi-row datasets—caching these would take up more memory, and SQLite's optimizer typically doesn't do this automatically unless there's a clear efficiency gain.

That said, there's an exception: you can explicitly trigger caching (called "materialization") using a materialized Common Table Expression (CTE). If you wrap your subquery in a WITH clause with the MATERIALIZED keyword, SQLite will store the subquery's results temporarily (in memory or on disk) so they don't need to be re-executed if referenced multiple times.

Example of materializing an EXECUTE LIST subquery:

WITH active_categories AS MATERIALIZED (SELECT id FROM categories WHERE is_active = 1)
SELECT product_name FROM products WHERE category_id IN (SELECT id FROM active_categories);

In this case, the active_categories subquery (marked as EXECUTE LIST in the plan) will have its results cached once, and the main query will reuse that cached dataset instead of re-running the subquery.

Quick Recap #

• EXECUTE LIST handles subqueries that return multiple rows
• Default behavior: no caching of results
• Caching is possible via explicit MATERIALIZED CTEs, or when the optimizer automatically decides materialization is optimal

内容的提问来源于stack exchange，提问作者Joe C