SQLite Forum

Most performant way to test for having 0, 1 or more rows in a table
Login
You can see the limiting in the EXPLAIN output, as it's more verbose than the shorter, more readable EXPLAIN QUERY PLAN output.

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (k integer primary key not null, data blob);
sqlite> .eqp full
sqlite> select (select count(*) from (select 1 from t limit 2)) = 1;
QUERY PLAN
|--SCAN CONSTANT ROW
`--SCALAR SUBQUERY 2
   |--CO-ROUTINE SUBQUERY 1
   |  `--SCAN t
   `--SCAN SUBQUERY 1
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     28    0                    0   Start at 28
1     Integer        22    3     0                    0   r[3]=22; return address
2     Once           0     22    0                    0
3     Null           0     4     4                    0   r[4..4]=NULL; Init subquery result
4     InitCoroutine  5     13    5                    0   SUBQUERY 1
5     Integer        2     6     0                    0   r[6]=2; LIMIT counter
6     OpenRead       1     2     0     0              0   root=2 iDb=0; t
7     Rewind         1     12    0                    0
8       Integer        1     7     0                    0   r[7]=1
9       Yield          5     0     0                    0
10      DecrJumpZero   6     12    0                    0   if (--r[6])==0 goto 12
11    Next           1     8     0                    1
12    EndCoroutine   5     0     0                    0
13    Integer        1     8     0                    0   r[8]=1; LIMIT counter
14    Null           0     9     9                    0   r[9..9]=NULL
15    InitCoroutine  5     0     5                    0
16      Yield          5     19    0                    0   next row of subquery_1
17      AggStep        0     0     9     count(0)       0   accum=r[9] step(r[0])
18    Goto           0     16    0                    0
19    AggFinal       9     0     0     count(0)       0   accum=r[9] N=0
20    Copy           9     4     0                    0   r[4]=r[9]
21    DecrJumpZero   8     22    0                    0   if (--r[8])==0 goto 22
22    Return         3     0     0                    0
23    Integer        1     1     0                    0   r[1]=1
24    Eq             10    26    4                    64  if r[4]==r[10] goto 26
25    ZeroOrNull     4     1     10                   0   r[1] = 0 OR NULL
26    ResultRow      1     1     0                    0   output=r[1]
27    Halt           0     0     0                    0
28    Transaction    0     0     1     0              1   usesStmtJournal=0
29    Integer        1     10    0                    0   r[10]=1
30    Goto           0     1     0                    0
0
sqlite>