SQLite - selecting from a view with conditions, when are rows filtered?

Question

The question is about SQLite. Suppose I have a view which is a result of a join of multiple tables, the tables are linked with foreign key constraints and all indexes are in place, and a select statement against such a view is executed and it uses a where condition which selects only a subset of the available rows. For each statement, the view must be 'materialized' (I don't know what the real word is), but is it materialized fully for all rows and the filtering from the select takes place then, or is the view materialized in a 'smart' way in which it already pre-filters the data somehow? Does it even matter? How does this work?

Answer 1

When you are querying a view like this:

SELECT ...
FROM MyView
WHERE ...

then the view gets merged as a subquery, like this:

SELECT ...
FROM (SELECT ...
      FROM ...
      WHERE ...)
WHERE ...

Futher processing is the same, whether the subquery originated from a view or was written explicitly in the query.

If possible, SQLite tries to flatten the subquery so that the end result is a single, simple query with all the joins and WHERE conditions merged together.

If that is not possible, SQLite tries to implement the subquery as a coroutine, i.e., it executes the inner query until it gets one row, and then applies any processing of the outer query to that row; then outputs the result, if any; then repeats.

Only if the inner query must compute all rows before returning the first one (for example, when using an ORDER BY that cannot be flattened), then using a coroutine is not possible, and the inner query is actually materialized into a temporary table.