JOIN is ~1.16× slower than IN (subquery)

JOIN vs `IN (subquery)` differ by roughly 10% and the direction wobbles between runs — not a gap you can generalise from (published at the edge: CV 9%).

Result

Spread over 3 runs: JOIN CV 11.9% / IN (subquery) CV 3.9% / Ratio CV 9.3%

The statement

SELECT i.id FROM items i JOIN tags t ON i.tag=t.tag WHERE t.label='BETA' LIMIT 500;

SELECT id FROM items WHERE tag IN (SELECT tag FROM tags WHERE label='BETA') LIMIT 500;

Conditions

100,000 rows plus a 5-row `tags` table; `items.tag` is not indexed.

Where this number does not apply

With a large `tags` table, or an index on `items.tag`, the planner may pick differently and the gap can invert.

Reproduce

cd bench && python sqbench.py --samples 9 --out ../data/run.json