SQLite Forum

I can agree that these queries should produce the same exact result sets, but the query planner certainly seems to distinguish them. All with v3.35.5 and problem size N=1000000:

- Mike's version plus Richard's hint (WHERE...IN)

```
EXPLAIN QUERY PLAN SELECT count(_rowid_) FROM edge
WHERE +node_from IN temp.sub_nodes AND node_to IN temp.sub_nodes

SEARCH TABLE edge USING COVERING INDEX edge_to_from (node_to=?)
USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
```

- anonymous' three-way join

```
EXPLAIN QUERY PLAN SELECT count(edge._rowid_) FROM edge,
     temp.sub_nodes sub_from ON sub_from.node_id = node_from,
     temp.sub_nodes sub_to ON sub_to.node_id = node_to

SCAN TABLE sub_nodes AS sub_from
SEARCH TABLE edge USING COVERING INDEX edge_from_to (node_from=?)
SEARCH TABLE sub_nodes AS sub_to USING INTEGER PRIMARY KEY (rowid=?)
```

- Keith's version plus Richard's hint (WHERE...EXISTS)

```
EXPLAIN QUERY PLAN SELECT count(_rowid_)
  FROM edge
 WHERE EXISTS (SELECT * FROM sub_nodes WHERE node_id == +node_from)
   AND EXISTS (SELECT * FROM sub_nodes WHERE node_id == node_to)

SEARCH TABLE edge USING COVERING INDEX edge_to_from (node_to=?)
USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
CORRELATED SCALAR SUBQUERY 1
    SEARCH TABLE sub_nodes USING INTEGER PRIMARY KEY (rowid=?)
```

- Mike's or Keith's version *without* Richard's hint (disastrously slow, using SeekScan)

```
SEARCH TABLE edge USING COVERING INDEX edge_to_from (node_to=? AND node_from=?)
USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR

```

--

Beauty is in the eye of the beholder, but my version is the most concise and most closely approximates what I'd say in English ("gimme the edges whose node_from and node_to are both in sub_nodes). And SQLite v3.33.0 did great with it even without the hint.

I'm certainly learning a ton from all this though, thanks!