In SQLite 3.24.0, both versions (inside/outside CTE) generate identical code. Maybe there is a difference if the CTE is used in a JOIN instead of directly, so the difference between "natural order" (inside cte) and "explicit order" (outside cte) becomes visible. explain with t(ord, val) as (values (1, 'two'), (2, 'one'), (3, 'five')) select val from t order by ord; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 00 NULL 1 InitCoroutine 1 12 2 00 NULL 2 Integer 1 2 0 00 NULL 3 String8 0 3 0 two 00 NULL 4 Yield 1 0 0 00 NULL 5 Integer 2 2 0 00 NULL 6 String8 0 3 0 one 00 NULL 7 Yield 1 0 0 00 NULL 8 Integer 3 2 0 00 NULL 9 String8 0 3 0 five 00 NULL 10 Yield 1 0 0 00 NULL 11 EndCoroutine 1 0 0 00 NULL 12 SorterOpen 1 3 0 k(1,B) 00 NULL 13 InitCoroutine 1 0 2 00 NULL 14 Yield 1 20 0 00 NULL 15 Copy 3 7 0 00 NULL 16 Copy 2 6 0 00 NULL 17 MakeRecord 6 2 8 00 NULL 18 SorterInsert 1 8 6 2 00 NULL 19 Goto 0 14 0 00 NULL 20 OpenPseudo 2 9 3 00 NULL 21 SorterSort 1 26 0 00 NULL 22 SorterData 1 9 2 00 NULL 23 Column 2 1 7 00 NULL 24 ResultRow 7 1 0 00 NULL 25 SorterNext 1 22 0 00 NULL 26 Halt 0 0 0 00 NULL