Does SQLite optimize a nested query by the outer constraints

Question

Suppose I have a query like this

select * 
from
  (select t.a as a from t) as t0
where
  t0.a = 10

Does SQLite fetch all data from t then filter out by t0.a = 10, or fetch data from t with t.a = 10 directly?

Answer 1

For simple cases like that, it does.

Here's how you I figured it out. Let's create a sample database with some data:

CREATE TABLE t(a integer primary key);
WITH seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n<100) INSERT INTO t SELECT * FROM seq;

Now we can ask sqlite to show us how it will execute various queries (the "query plan") with EXPLAIN QUERY PLAN:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM t WHERE a=10;
0|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?)

and for when we use a subquery:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t) t0 WHERE t0.a=10;
0|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?)

As you can see, it uses the index in both cases. In fact, the plans are identical.

In general, you should use EXPLAIN QUERY PLAN to see how sqlite executes a query and determine this way if you need to create some indexes, modify the query etc.