sql-cli 1.82.4

SQL query tool for CSV/JSON with both interactive TUI and non-interactive CLI modes - perfect for exploration and automation
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

# Tier 6 — CTEs and set operations.

[[case]]
id = "cte_single"
data = "international_sales.csv"
sql = "WITH x AS (SELECT region, amount FROM international_sales) SELECT region, amount FROM x"

[[case]]
id = "cte_multiple"
data = "international_sales.csv"
sql = "WITH a AS (SELECT region, SUM(amount) AS total FROM international_sales GROUP BY region), b AS (SELECT region FROM a WHERE total > 5000) SELECT region FROM b"

[[case]]
id = "cte_referencing_cte"
data = "international_sales.csv"
sql = "WITH base AS (SELECT region, amount FROM international_sales), agg AS (SELECT region, SUM(amount) AS total FROM base GROUP BY region) SELECT region, total FROM agg"

[[case]]
id = "union"
data = "trades.csv"
sql = "SELECT symbol FROM trades WHERE price > 186 UNION SELECT symbol FROM trades WHERE price < 184"

[[case]]
id = "union_all"
data = "international_sales.csv"
sql = "SELECT region FROM international_sales WHERE amount > 2000 UNION ALL SELECT region FROM international_sales WHERE amount < 100"

[[case]]
id = "intersect"
data = "international_sales.csv"
sql = "SELECT region FROM international_sales WHERE amount > 2000 INTERSECT SELECT region FROM international_sales WHERE amount < 1000"
# Was P6 ("INTERSECT is not yet implemented"). Implemented 2026-07-25 in the
# set-op path; INTERSECT DISTINCT keeps rows present in both sides. Returns
# {Europe} — a non-trivial result (some regions are on only one side).

[[case]]
id = "except"
data = "international_sales.csv"
sql = "SELECT region FROM international_sales EXCEPT SELECT region FROM international_sales WHERE amount > 3000"
# Was P6 ("EXCEPT is not yet implemented"). Implemented 2026-07-25 alongside
# INTERSECT. Threshold is 3000 (not 2000) deliberately: every region has a sale
# > 2000, so that form is trivially empty and would pass for the wrong reason.
# > 3000 leaves {Oceania} — a real left-minus-right difference.

[[case]]
id = "recursive_cte"
data = "trades.csv"
sql = "WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n"
# Recursive CTEs are a known, considered, deferred design item (not supported).
expect = "GAP"

[[case]]
id = "cte_in_expression_position"
data = "trades.csv"
sql = "SELECT symbol, price FROM trades WHERE price > (WITH avg_cte AS (SELECT AVG(price) AS a FROM trades) SELECT a FROM avg_cte)"
# Was P12: WITH was rejected in expression position ("Unexpected token in
# primary expression: With"). Fixed 2026-07-25 — the subquery-detection guards
# now accept a leading WITH (parse_subquery already dispatched it to the CTE
# parser). DuckDB allows a CTE inside a scalar subquery; now AGREE.