Queries
Reading and transforming data — select shapes output, from iterates sources, and subsequent clauses filter, sort, group, or nest results.
Select
Envelope Object
Select . emits the binding tuple as an envelope object.
SQL
create table T;
insert into T ({"x": 1});
select . from T as t;
Result
[
{ "t": { "x": 1 } }
]
Bindings Flat
Select * spreads bindings flat.
SQL
create table T;
insert into T ({"x": 1, "y": 2});
select * from T as t;
Result
[
{ "x": 1, "y": 2 }
]
Per Row
Select
SQL
create table T;
insert into T ({"x": 1}, {"x": 2});
select t.x from T as t order by t.x;
Result
[
1,
2
]
Per Row
Select
SQL
create table T;
insert into T ({"x": 1}, {"x": 2});
select 7 from T as t order by t.x;
Result
[
7,
7
]
Per Row
Select
SQL
create table T;
insert into T ({"x": 1});
select {"a": t.x} from T as t;
Result
[
{ "a": 1 }
]
Named Field
Select
SQL
create table T;
insert into T ({"x": 10});
select t.x as a from T as t;
Result
[
{ "a": 10 }
]
Named Member
A list of items emits an object with each named member.
SQL
create table T;
insert into T ({"x": 1, "y": 2});
select t.x as a, t.y as b from T as t;
Result
[
{ "a": 1, "b": 2 }
]
List Items
List items may be arbitrary expressions, not only paths.
SQL
create table T;
insert into T ({"x": 1});
select 1 as a, 'hi' as b from T as t;
Result
[
{ "a": 1, "b": "hi" }
]
From
From
SQL
create table T;
insert into T ({"x": 1});
select T.x from T;
Result
[
1
]
From
From
SQL
create table T;
insert into T ({"x": 7});
select t.x from T as t;
Result
[
7
]
From
From
SQL
create table T;
insert into T ({"x": 9});
select t.x from T t;
Result
[
9
]
An Array
An array literal builds an array from its element expressions.
SQL
create table T;
insert into T ({"x": 7});
select [1, 2, t.x] as a from T as t;
Result
[
{ "a": [ 1, 2, 7 ] }
]
Array Literals
Array literals may nest.
SQL
create table T;
insert into T ({"x": 1});
select [[1, 2], [3]] as a from T as t;
Result
[
{ "a": [ [ 1, 2 ], [ 3 ] ] }
]
Single Row
Select
SQL
create table T;
select 1;
Result
[
1
]
Nothing Spread
Select * requires a From clause (nothing to spread).
SQL
create table T;
select *;
Expected error: static
Tuple Envelope
Select . requires a From clause (no binding tuple to envelope).
SQL
create table T;
select .;
Expected error: static
From
Scanning An
Scanning an empty table yields no rows.
SQL
create table T;
select * from T;
Result
[]
Insertion Order
A table scan returns all rows in insertion order.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Omitting As
Omitting as uses the table name as the alias.
SQL
create table T;
insert into T ({"x": 42});
select T.x from T;
Result
[
42
]
An Explicit
An explicit as alias names the binding.
SQL
create table T;
insert into T ({"x": 10});
select t.x from T as t;
Result
[
10
]
Row Unwrapped
Select * over one source emits the row unwrapped.
SQL
create table T;
insert into T ({"x": 1, "y": 2});
select * from T as t;
Result
[
{ "x": 1, "y": 2 }
]
Its Alias
Select . wraps the binding under its alias.
SQL
create table T;
insert into T ({"x": 1});
select . from T as t;
Result
[
{ "t": { "x": 1 } }
]
Two Comma
Two comma sources form a Cartesian product, merged by select *.
SQL
create table T;
create table S;
insert into S ({"b": 10}, {"b": 20});
insert into T ({"a": 1}, {"a": 2});
select * from T as t, S as s;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 },
{ "a": 2, "b": 10 },
{ "a": 2, "b": 20 }
]
From Cross Projection
A projection list may reference both cross-joined bindings.
SQL
create table T;
create table S;
insert into S ({"b": 10}, {"b": 20});
insert into T ({"a": 1}, {"a": 2});
select t.a as a, s.b as b from T as t, S as s;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 },
{ "a": 2, "b": 10 },
{ "a": 2, "b": 20 }
]
Dot Envelope
Select . over two sources keys each binding by its alias.
SQL
create table T;
create table S;
insert into S ({"b": 10});
insert into T ({"a": 1});
select . from T as t, S as s;
Result
[
{ "t": { "a": 1 }, "s": { "b": 10 } }
]
Both Bindings
A where predicate filters the product across both bindings.
SQL
create table T;
create table S;
insert into S ({"b": 10}, {"b": 20});
insert into T ({"a": 1}, {"a": 2});
select t.a as a, s.b as b from T as t, S as s where t.a = 1;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 }
]
An Empty
An empty inner source makes the whole product empty.
SQL
create table T;
create table S;
insert into T ({"a": 1}, {"a": 2});
select * from T as t, S as s;
Result
[]
Referencing An
Referencing an undeclared table is a static error.
SQL
create table T;
select * from Ghost;
Expected error: static
Referencing An
Referencing an alias not in scope is a static error.
SQL
create table T;
select x.foo from T;
Expected error: static
Earlier Binding
A later source may unnest a collection path on an earlier binding.
SQL
create table T;
insert into T ({"items": [1, 2, 3]});
select t.items as items, item as item from T as t, t.items as item;
Result
[
{ "items": [ 1, 2, 3 ], "item": 1 },
{ "items": [ 1, 2, 3 ], "item": 2 },
{ "items": [ 1, 2, 3 ], "item": 3 }
]
Star Scalar
Select * keeps a non-object (scalar) lateral binding under its alias.
SQL
create table T;
insert into T ({"items": [1, 2, 3]});
select * from T as t, t.items as item;
Result
[
{ "items": [ 1, 2, 3 ], "item": 1 },
{ "items": [ 1, 2, 3 ], "item": 2 },
{ "items": [ 1, 2, 3 ], "item": 3 }
]
The Unnested
The unnested element binds under its alias in the envelope.
SQL
create table T;
insert into T ({"items": [1, 2]});
select . from T as t, t.items as item;
Result
[
{ "t": { "items": [ 1, 2 ] }, "item": 1 },
{ "t": { "items": [ 1, 2 ] }, "item": 2 }
]
Unnest Flattens
Unnest flattens across every outer row in order.
SQL
create table T;
insert into T ({"k": 1, "items": [10, 11]}, {"k": 2, "items": [20]});
select t.k as k, item as v from T as t, t.items as item;
Result
[
{ "k": 1, "v": 10 },
{ "k": 1, "v": 11 },
{ "k": 2, "v": 20 }
]
An Empty
An empty collection contributes no rows for that outer binding.
SQL
create table T;
insert into T ({"items": []});
select item as v from T as t, t.items as item;
Result
[]
Missing Path
A missing path is treated as empty (inner-join-like).
SQL
create table T;
insert into T ({"x": 1});
select item as v from T as t, t.items as item;
Result
[]
Non Array
A non-array source value contributes no rows.
SQL
create table T;
insert into T ({"items": 5});
select item as v from T as t, t.items as item;
Result
[]
An Array
An array literal may serve as a From source, scanned element-wise.
SQL
create table T;
select x from [1, 2, 3] as x;
Result
[
1,
2,
3
]
Self Reference
A lateral source may not reference its own alias.
SQL
create table T;
select * from T as t, item.x as item;
Expected error: static
Requires Alias
A lateral collection source requires an alias.
SQL
create table T;
select * from T as t, t.items;
Expected error: static
An Empty
An empty array literal source yields no rows.
SQL
create table T;
select x from [] as x;
Result
[]
Its Alias
Select . wraps each scanned element under its alias.
SQL
create table T;
select . from [1, 2] as x;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Into Them
A value source iterates object elements and may path into them.
SQL
create table T;
select x.a as a from [{"a": 1}, {"a": 2}] as x;
Result
[
{ "a": 1 },
{ "a": 2 }
]
Literal Csv
A file-path string literal desugars to read_csv.
SQL
create table T;
select * from 'tests/fixtures/people.csv' order by people.name;
Result
[
{ "name": "alice", "age": 30 },
{ "name": "bob", "age": 25 }
]
Read_csv In
Read_csv in FROM with an explicit alias.
SQL
create table T;
select r.name as name from read_csv('tests/fixtures/people.csv') as r order by r.name;
Result
[
{ "name": "alice" },
{ "name": "bob" }
]
Non Array
A non-array value source contributes no rows.
SQL
create table T;
select x from 5 as x;
Result
[]
Table Row
A value source re-iterates for every outer table row.
SQL
create table T;
insert into T ({"a": 1}, {"a": 2});
select . from T as t, [10, 20] as n;
Result
[
{ "t": { "a": 1 }, "n": 10 },
{ "t": { "a": 1 }, "n": 20 },
{ "t": { "a": 2 }, "n": 10 },
{ "t": { "a": 2 }, "n": 20 }
]
Unnest An
Unnest an array of objects and path into each element.
SQL
create table T;
insert into T ({"s": [{"x": 1}, {"x": 2}]}, {"s": [{"x": 3}]});
select s.x as x from T as t, t.s as s;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Where
Constant True
Constant true keeps all rows.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2});
select * from T where true;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Constant False
Constant false drops all rows.
SQL
create table T;
insert into T ({"x": 1});
select * from T where false;
Result
[]
Null Predicate
Null predicate is not-true and drops all rows.
SQL
create table T;
insert into T ({"x": 1});
select * from T where null;
Result
[]
Numeric Greater-than
Numeric greater-than filters by oid insertion order.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select * from T where T.x > 1;
Result
[
{ "x": 2 },
{ "x": 3 }
]
Numeric Equality
Numeric equality matches a single row.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select * from T where T.x = 2;
Result
[
{ "x": 2 }
]
Numeric Inequality
Numeric inequality excludes matching value.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select * from T where T.x != 1;
Result
[
{ "x": 2 },
{ "x": 3 }
]
String Equality
String equality in where.
SQL
create table T;
create table S;
insert into S ({"name": 'alice'}, {"name": 'bob'});
select * from S where S.name = 'bob';
Result
[
{ "name": "bob" }
]
Boolean Equality
Boolean equality in where.
SQL
create table T;
insert into T ({"flag": true}, {"flag": false});
select * from T where T.flag = true;
Result
[
{ "flag": true }
]
Predicate May
Predicate may use an explicit from alias.
SQL
create table T;
insert into T ({"x": 0}, {"x": 1});
select * from T as t where t.x > 0;
Result
[
{ "x": 1 }
]
Under 3VL,
Under 3VL, x = null is unknown so the row is excluded (use IS NULL).
SQL
create table T;
insert into T ({"x": null});
select * from T where T.x = null;
Result
[]
Null Member
Null member fails inequality against non-null.
SQL
create table T;
insert into T ({"x": null});
select * from T where T.x != 1;
Result
[]
Absent Field
Absent field reads as null, so x = null is unknown and excluded.
SQL
create table T;
insert into T ({});
select * from T where T.x = null;
Result
[]
Absent Field
Absent field reads as null and fails ordering comparison.
SQL
create table T;
insert into T ({});
select * from T where T.x > 0;
Result
[]
Only Rows
Only rows with present matching field pass equality.
SQL
create table T;
insert into T ({"x": 1}, {});
select * from T where T.x = 1;
Result
[
{ "x": 1 }
]
Scalar Projection
Scalar projection with where (moved from from-clause suite).
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select T.x from T where T.x > 1;
Result
[
2,
3
]
Order by
Order By
Order by sorts ascending by default.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2});
select t.x from T as t order by t.x;
Result
[
1,
2,
3
]
Explicit Asc
Explicit asc matches the default.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2});
select t.x from T as t order by t.x asc;
Result
[
1,
2,
3
]
Desc Sorts Descending
Desc sorts descending.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2});
select t.x from T as t order by t.x desc;
Result
[
3,
2,
1
]
Order By
Order by reorders whole rows under select *.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2});
select * from T as t order by t.x;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Multiple Keys
Multiple keys sort left-to-right with per-key direction.
SQL
create table T;
insert into T ({"a": 1, "b": 2}, {"a": 1, "b": 1}, {"a": 2, "b": 5});
select * from T as t order by t.a, t.b desc;
Result
[
{ "a": 1, "b": 2 },
{ "a": 1, "b": 1 },
{ "a": 2, "b": 5 }
]
Null Sorts
Null sorts after all values in ascending order.
SQL
create table T;
insert into T ({"x": 2}, {"x": null}, {"x": 1});
select t.x from T as t order by t.x;
Result
[
1,
2,
null
]
Null Sorts
Null sorts before all values in descending order.
SQL
create table T;
insert into T ({"x": 2}, {"x": null}, {"x": 1});
select t.x from T as t order by t.x desc;
Result
[
null,
2,
1
]
Ints And
Ints and floats interleave by numeric value.
SQL
create table T;
insert into T ({"x": 2}, {"x": 1.5}, {"x": 1});
select t.x from T as t order by t.x;
Result
[
1,
1.5,
2
]
Strings Sort Lexicographically
Strings sort lexicographically.
SQL
create table T;
insert into T ({"s": "banana"}, {"s": "apple"}, {"s": "cherry"});
select t.s from T as t order by t.s;
Result
[
"apple",
"banana",
"cherry"
]
Mixed Types
Mixed types sort by the same total order as the comparison operators (bool < number < string < null).
SQL
create table T;
insert into T ({"x": "s"}, {"x": null}, {"x": 1}, {"x": true});
select t.x from T as t order by t.x;
Result
[
true,
1,
"s",
null
]
Order By
Order by then limit yields the top N.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2}, {"x": 5}, {"x": 4});
select t.x from T as t order by t.x desc limit 2;
Result
[
5,
4
]
Order Sorts
Order sorts the post-where stream.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2}, {"x": 4});
select t.x from T as t where t.x > 1 order by t.x;
Result
[
2,
3,
4
]
Order By
Order by sorts the cross product of two sources.
SQL
create table T;
create table S;
insert into T ({"x": 2}, {"x": 1});
insert into S ({"y": 9});
select * from T as t, S as s order by t.x;
Result
[
{ "x": 1, "y": 9 },
{ "x": 2, "y": 9 }
]
Limit
Limit N
Limit N takes the first N rows in scan order.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});
select * from T limit 2;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Limit 0
Limit 0 emits no rows.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2});
select * from T limit 0;
Result
[]
Limit Greater
Limit greater than the row count returns all rows.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2});
select * from T limit 10;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Limit N..
Limit N.. skips the first N rows and keeps the rest.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});
select * from T limit 2..;
Result
[
{ "x": 3 },
{ "x": 4 },
{ "x": 5 }
]
Skipping Past
Skipping past the end yields no rows.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select * from T limit 5..;
Result
[]
Limit 0..
Limit 0.. skips nothing and returns all rows.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select * from T limit 0..;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Limit N..M
Limit N..M is half-open over indices [N, M).
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});
select * from T limit 1..3;
Result
[
{ "x": 2 },
{ "x": 3 }
]
Limit Slice Empty
A slice with M == N emits nothing.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});
select * from T limit 3..3;
Result
[]
Last Row
A slice whose end runs past the data takes through the last row.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4});
select * from T limit 1..10;
Result
[
{ "x": 2 },
{ "x": 3 },
{ "x": 4 }
]
Limit Slices
Limit slices the post-where stream, not the raw scan.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4});
select * from T where T.x > 1 limit 2;
Result
[
{ "x": 2 },
{ "x": 3 }
]
Limit Applies
Limit applies to a scalar projection.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select T.x from T limit 2;
Result
[
1,
2
]
Aggregate
Count(*) Counts
Count(*) counts every row.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select count(*) from T as t;
Result
[
3
]
Count(expr) Counts
Count(expr) counts only rows where expr is non-null.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": null});
select count(t.x) from T as t;
Result
[
2
]
Count(*) Includes
Count(*) includes rows with a null column, unlike count(expr).
SQL
create table T;
insert into T ({"x": 1}, {"x": null});
select count(*) from T as t;
Result
[
2
]
Sum Of Integers
Sum of integers.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select sum(t.x) from T as t;
Result
[
6
]
Sum Of
Sum of floats (and mixed int/float) is a float.
SQL
create table T;
insert into T ({"x": 1.5}, {"x": 2}, {"x": 0.25});
select sum(t.x) from T as t;
Result
[
3.75
]
Sum Skips
Sum skips null inputs.
SQL
create table T;
insert into T ({"x": 10}, {"x": null}, {"x": 5});
select sum(t.x) from T as t;
Result
[
15
]
An Integer
An integer sum that overflows i64 promotes to a float (sqlite-faithful).
SQL
create table T;
insert into T ({"x": 9223372036854775807}, {"x": 9223372036854775807});
select sum(t.x) from T as t;
Result
[
18446744073709552000
]
Overflow Errors
A float sum that overflows to non-finite is a runtime error, not a silent null.
SQL
create table T;
insert into T ({"x": 1e308}, {"x": 1e308});
select sum(t.x) from T as t;
Expected error: runtime
Min Of Integers
Min of integers.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2});
select min(t.x) from T as t;
Result
[
1
]
Max Of Integers
Max of integers.
SQL
create table T;
insert into T ({"x": 3}, {"x": 1}, {"x": 2});
select max(t.x) from T as t;
Result
[
3
]
Min Of
Min of strings is lexicographic.
SQL
create table T;
insert into T ({"s": "banana"}, {"s": "apple"}, {"s": "cherry"});
select min(t.s) from T as t;
Result
[
"apple"
]
Max Of
Max of strings is lexicographic.
SQL
create table T;
insert into T ({"s": "banana"}, {"s": "apple"}, {"s": "cherry"});
select max(t.s) from T as t;
Result
[
"cherry"
]
Min/max Skip
Min/max skip null inputs.
SQL
create table T;
insert into T ({"x": 2}, {"x": null}, {"x": 5});
select min(t.x) from T as t;
Result
[
2
]
Avg Returns
Avg returns a float mean.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2});
select avg(t.x) from T as t;
Result
[
1.5
]
Avg Divides
Avg divides the sum by the non-null count only.
SQL
create table T;
insert into T ({"x": 2}, {"x": null}, {"x": 4});
select avg(t.x) from T as t;
Result
[
3
]
Count(*) Over
Count(*) over an empty table is 0 (one row).
SQL
create table T;
select count(*) from T as t;
Result
[
0
]
Sum Over
Sum over an empty table is null (one row).
SQL
create table T;
select sum(t.x) from T as t;
Result
[
null
]
Min Over
Min over an empty table is null (one row).
SQL
create table T;
select min(t.x) from T as t;
Result
[
null
]
Max Over
Max over an empty table is null (one row).
SQL
create table T;
select max(t.x) from T as t;
Result
[
null
]
Avg Over
Avg over an empty table is null (one row).
SQL
create table T;
select avg(t.x) from T as t;
Result
[
null
]
Aggregates Over
Aggregates over an all-null column — count(expr) 0, others null.
SQL
create table T;
insert into T ({"x": null}, {"x": null});
select count(t.x) from T as t;
Result
[
0
]
SQL
create table T;
insert into T ({"x": null}, {"x": null});
select count(t.x) from T as t;
select sum(t.x) from T as t;
Result
[
null
]
SQL
create table T;
insert into T ({"x": null}, {"x": null});
select count(t.x) from T as t;
select sum(t.x) from T as t;
select max(t.x) from T as t;
Result
[
null
]
SQL
create table T;
insert into T ({"x": null}, {"x": null});
select count(t.x) from T as t;
select sum(t.x) from T as t;
select max(t.x) from T as t;
select avg(t.x) from T as t;
Result
[
null
]
Aggregation Runs
Aggregation runs over the post-where stream.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select count(*) from T as t where t.x > 1;
Result
[
2
]
Several Aggregates
Several aggregates project into one object row.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select { "c": count(*), "s": sum(t.x), "m": max(t.x) } from T as t;
Result
[
{ "c": 3, "s": 6, "m": 3 }
]
Arithmetic Over
Arithmetic over an aggregate is allowed (the agg is folded, then combined).
SQL
create table T;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select sum(t.x) + 1 from T as t;
Result
[
7
]
Limit Applies
Limit applies to the single aggregate row.
SQL
create table T;
insert into T ({"x": 1}, {"x": 2});
select count(*) from T as t limit 0;
Result
[]
An Aggregate
An aggregate in WHERE is a static (bind) error.
SQL
create table T;
select count(*) from T as t where count(*) > 0;
Expected error: static
An Aggregate
An aggregate nested inside another is a static (bind) error.
SQL
create table T;
select sum(count(t.x)) from T as t;
Expected error: static
Only Count
Only count accepts the star form; sum(*) is a static error.
SQL
create table T;
select sum(*) from T as t;
Expected error: static
Column Rejected
A bare column reference alongside an aggregate is unsupported (no GROUP BY).
SQL
create table T;
select { "c": count(*), "x": t.x } from T as t;
Expected error: static
Min Over
Min over mixed types follows the total order (number < string).
SQL
create table T;
insert into T ({"v": 1}, {"v": "a"});
select min(t.v) from T as t;
Result
[
1
]
group
Count(*) Per
Count(*) per group, one row per distinct key in key order.
SQL
create table T;
insert into T ({"g": "a"}, {"g": "b"}, {"g": "a"}, {"g": "a"}, {"g": "b"});
select { "g": t.g, "n": count(*) } from T as t group by t.g;
Result
[
{ "g": "a", "n": 3 },
{ "g": "b", "n": 2 }
]
The Expr
The expr as name list projection form works under grouping.
SQL
create table T;
insert into T ({"g": "a"}, {"g": "b"}, {"g": "a"});
select t.g as g, count(*) as n from T as t group by t.g;
Result
[
{ "g": "a", "n": 2 },
{ "g": "b", "n": 1 }
]
Integer Group
Integer group keys come out in ascending key order.
SQL
create table T;
insert into T ({"k": 3}, {"k": 1}, {"k": 2}, {"k": 1}, {"k": 3});
select { "k": t.k, "n": count(*) } from T as t group by t.k;
Result
[
{ "k": 1, "n": 2 },
{ "k": 2, "n": 1 },
{ "k": 3, "n": 2 }
]
Every Aggregate
Every aggregate folds independently within each group.
SQL
create table T;
insert into T ({"g": "a", "v": 10}, {"g": "a", "v": 20}, {"g": "b", "v": 5});
select { "g": t.g, "s": sum(t.v), "a": avg(t.v), "mn": min(t.v), "mx": max(t.v) } from T as t group by t.g;
Result
[
{ "g": "a", "s": 30, "a": 15, "mn": 10, "mx": 20 },
{ "g": "b", "s": 5, "a": 5, "mn": 5, "mx": 5 }
]
Count(expr) Skips
Count(expr) skips nulls inside each group.
SQL
create table T;
insert into T ({"g": "a", "v": 1}, {"g": "a", "v": null}, {"g": "b", "v": 2});
select { "g": t.g, "n": count(t.v) } from T as t group by t.g;
Result
[
{ "g": "a", "n": 1 },
{ "g": "b", "n": 1 }
]
Grouping On
Grouping on two keys orders by the first then the second.
SQL
create table T;
insert into T ({"a": 1, "b": 1}, {"a": 1, "b": 2}, {"a": 1, "b": 1}, {"a": 2, "b": 1});
select { "a": t.a, "b": t.b, "n": count(*) } from T as t group by t.a, t.b;
Result
[
{ "a": 1, "b": 1, "n": 2 },
{ "a": 1, "b": 2, "n": 1 },
{ "a": 2, "b": 1, "n": 1 }
]
An Arbitrary
An arbitrary key expression groups, and the same expression projects it.
SQL
create table T;
insert into T ({"x": 1, "y": 1}, {"x": 2, "y": 0}, {"x": 0, "y": 3});
select { "s": t.x + t.y, "n": count(*) } from T as t group by t.x + t.y;
Result
[
{ "s": 2, "n": 2 },
{ "s": 3, "n": 1 }
]
Grouping With
Grouping with no aggregate behaves like DISTINCT over the key.
SQL
create table T;
insert into T ({"c": "x"}, {"c": "y"}, {"c": "x"}, {"c": "x"});
select t.c from T as t group by t.c;
Result
[
"x",
"y"
]
Rows Whose
Rows whose key is null form a single group, sorted last.
SQL
create table T;
insert into T ({"g": "a", "v": 1}, {"g": "a", "v": 2}, {"v": 3});
select { "g": t.g, "n": count(*) } from T as t group by t.g;
Result
[
{ "g": "a", "n": 2 },
{ "g": null, "n": 1 }
]
An Empty
An empty input yields zero groups (contrast ungrouped count -> one row).
SQL
create table T;
select { "g": t.g, "n": count(*) } from T as t group by t.g;
Result
[]
WHERE Filters
WHERE filters rows before they are grouped.
SQL
create table T;
insert into T ({"g": "a", "v": 1}, {"g": "a", "v": 5}, {"g": "b", "v": 2});
select { "g": t.g, "n": count(*) } from T as t where t.v > 1 group by t.g;
Result
[
{ "g": "a", "n": 1 },
{ "g": "b", "n": 1 }
]
HAVING Filters
HAVING filters whole groups by an aggregate predicate.
SQL
create table T;
insert into T ({"g": "a"}, {"g": "a"}, {"g": "b"});
select { "g": t.g, "n": count(*) } from T as t group by t.g having count(*) > 1;
Result
[
{ "g": "a", "n": 2 }
]
HAVING May
HAVING may reference a group key (read as the group's value).
SQL
create table T;
insert into T ({"g": "a"}, {"g": "b"});
select t.g from T as t group by t.g having t.g = "b";
Result
[
"b"
]
LIMIT Takes
LIMIT takes the first N groups in key order.
SQL
create table T;
insert into T ({"g": "a"}, {"g": "b"}, {"g": "c"});
select t.g from T as t group by t.g limit 2;
Result
[
"a",
"b"
]
LIMIT N..M
LIMIT N..M skips then takes over the grouped stream.
SQL
create table T;
insert into T ({"g": "a"}, {"g": "b"}, {"g": "c"});
select t.g from T as t group by t.g limit 1..3;
Result
[
"b",
"c"
]
LIMIT Counts
LIMIT counts only groups that survived HAVING.
SQL
create table T;
insert into T ({"g": "a"}, {"g": "a"}, {"g": "b"}, {"g": "c"}, {"g": "c"});
select { "g": t.g, "n": count(*) } from T as t group by t.g having count(*) > 1 limit 1;
Result
[
{ "g": "a", "n": 2 }
]
Projection Rejected
A projected column that is neither grouped nor aggregated is a static error.
SQL
create table T;
select { "g": t.g, "v": t.v } from T as t group by t.g;
Expected error: static
Group Rejected
Select * has no defined columns under grouping — static error.
SQL
create table T;
select * from T as t group by t.g;
Expected error: static
Group Rejected
Select . (the binding tuple) is undefined under grouping — static error.
SQL
create table T;
select . from T as t group by t.g;
Expected error: static
An Aggregate
An aggregate in a GROUP BY key is a static error.
SQL
create table T;
select count(*) from T as t group by count(*);
Expected error: static
ORDER BY
ORDER BY over a grouped query is not supported yet — static error.
SQL
create table T;
select t.g from T as t group by t.g order by t.g;
Expected error: static
Having Rejected
A bare non-grouped column in HAVING is a static error.
SQL
create table T;
select t.g from T as t group by t.g having t.v > 0;
Expected error: static
subquery
Scalar Uncorrelated Count
A scalar subquery in projection with no outer from.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select (select count(*) from T as t);
Result
[
3
]
Scalar Aliased Member
A scalar subquery aliased into a projection member.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select (select count(*) from T as t) as n;
Result
[
{ "n": 3 }
]
Is null
A scalar subquery over an empty bag coerces to null.
SQL
create table T;
create table S;
select (select t.x from T as t);
Result
[
null
]
An Aggregate
An aggregate scalar subquery over an empty table is null.
SQL
create table T;
create table S;
select (select max(t.x) from T as t);
Result
[
null
]
Many Rows
A scalar subquery returning more than one row is a runtime error.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
select (select t.x from T as t);
Expected error: runtime
Scalar In Where
A scalar subquery as a where comparand.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
insert into S ({"v": 2}, {"v": 3});
select t.x from T as t where t.x = (select max(s.v) from S as s);
Result
[
3
]
Scalar Correlated
A correlated scalar subquery re-runs per outer row.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"g": 1}, {"g": 1}, {"g": 2});
select t.x as x, (select count(*) from S as s where s.g = t.x) as n from T as t;
Result
[
{ "x": 1, "n": 2 },
{ "x": 2, "n": 1 }
]
Scalar Single Row
A non-aggregate scalar subquery over exactly one row is that value.
SQL
create table T;
create table S;
insert into T ({"x": 7});
select (select t.x from T as t);
Result
[
7
]
Scalar In Expr
A scalar subquery composes as an operand of an arithmetic expression.
SQL
create table T;
create table S;
insert into T ({"x": 10}, {"x": 20});
insert into S ({"v": 5});
select t.x + (select max(s.v) from S as s) as y from T as t;
Result
[
{ "y": 15 },
{ "y": 25 }
]
Outer Query
A derived table filters then projects in the outer query.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select d.x from (select t.x as x from T as t where t.x > 1) as d;
Result
[
2,
3
]
Derived Star
Select * over a derived table.
SQL
create table T;
create table S;
insert into T ({"x": 1, "y": 2});
select * from (select t.x as x, t.y as y from T as t) as d;
Result
[
{ "x": 1, "y": 2 }
]
Derived Correlated Lateral
A lateral derived table references an earlier from binding.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"g": 1, "v": 10}, {"g": 1, "v": 11}, {"g": 2, "v": 20});
select t.x as x, d.n as n from T as t, (select s.v as n from S as s where s.g = t.x) as d;
Result
[
{ "x": 1, "n": 10 },
{ "x": 1, "n": 11 },
{ "x": 2, "n": 20 }
]
Derived Group By
A derived table containing group by (sink through cc_group).
SQL
create table T;
create table S;
insert into T ({"g": "a"}, {"g": "b"}, {"g": "a"});
select d.g as g, d.n as n from (select t.g as g, count(*) as n from T as t group by t.g) as d;
Result
[
{ "g": "a", "n": 2 },
{ "g": "b", "n": 1 }
]
Derived Order Limit
A derived table containing order by + limit (sink through cc_order).
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select d.x from (select t.x as x from T as t order by t.x desc limit 2) as d;
Result
[
3,
2
]
Derived Requires Alias
A derived-table source requires an alias.
SQL
create table T;
create table S;
select x from (select t.x as x from T as t);
Expected error: static
An Uncorrelated
An uncorrelated derived table cross-joined with a base table.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"v": 2}, {"v": 3});
select t.x as x, d.v as v from T as t, (select s.v as v from S as s where s.v = 2) as d;
Result
[
{ "x": 1, "v": 2 },
{ "x": 2, "v": 2 }
]
Nested Derived
A derived table whose source is itself a derived table.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
select dd.x from (select d.x as x from (select t.x as x from T as t where t.x > 1) as d) as dd;
Result
[
2,
3
]
Membership Against
Membership against a subquery bag.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
insert into S ({"v": 2}, {"v": 3});
select t.x from T as t where t.x in (select s.v from S as s);
Result
[
2,
3
]
Non-membership Against
Non-membership against a subquery bag.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
insert into S ({"v": 2}, {"v": 3});
select t.x from T as t where t.x not in (select s.v from S as s);
Result
[
1
]
In Over
In over an empty bag matches nothing.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
select t.x from T as t where t.x in (select s.v from S as s);
Result
[]
Not In
Not in over an empty bag matches everything.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
select t.x from T as t where t.x not in (select s.v from S as s);
Result
[
1,
2
]
Not In
Not in with a null element is unknown (3VL), excluding the row.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"v": 2}, {"v": null});
select t.x from T as t where t.x not in (select s.v from S as s);
Result
[]
In null Operand
A null left operand of in is unknown (3VL), excluding the row.
SQL
create table T;
create table S;
insert into T ({"x": null}, {"x": 2});
insert into S ({"v": 2}, {"v": 3});
select t.x from T as t where t.x in (select s.v from S as s);
Result
[
2
]
Exists Over
Exists over a correlated subquery.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"g": 1});
select t.x from T as t where exists (select s.g from S as s where s.g = t.x);
Result
[
1
]
Not Exists
Not exists over a correlated subquery.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"g": 1});
select t.x from T as t where not exists (select s.g from S as s where s.g = t.x);
Result
[
2
]
Exists Over
Exists over a non-empty uncorrelated subquery is true for all rows.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"g": 9});
select t.x from T as t where exists (select s.g from S as s);
Result
[
1,
2
]
Exists Over
Exists over an empty subquery is false.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
select t.x from T as t where exists (select s.g from S as s);
Result
[]
X >
X > any (bag) is true when x exceeds the minimum.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 5}, {"x": 10});
insert into S ({"v": 3}, {"v": 8});
select t.x from T as t where t.x > any (select s.v from S as s);
Result
[
5,
10
]
X >
X > all (bag) is true when x exceeds the maximum.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 5}, {"x": 10});
insert into S ({"v": 3}, {"v": 8});
select t.x from T as t where t.x > all (select s.v from S as s);
Result
[
10
]
X =
X = any (bag) is equivalent to in.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
insert into S ({"v": 2}, {"v": 3});
select t.x from T as t where t.x = any (select s.v from S as s);
Result
[
2,
3
]
Any Over
Any over an empty bag is false.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
select t.x from T as t where t.x > any (select s.v from S as s);
Result
[]
All Over
All over an empty bag is true.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
select t.x from T as t where t.x > all (select s.v from S as s);
Result
[
1,
2
]
All With
All with a null element is unknown (3VL), excluding the row.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 10});
insert into S ({"v": 3}, {"v": null});
select t.x from T as t where t.x > all (select s.v from S as s);
Result
[]
X <
X < all (bag) is true when x is below the minimum.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 5}, {"x": 10});
insert into S ({"v": 3}, {"v": 8});
select t.x from T as t where t.x < all (select s.v from S as s);
Result
[
1
]
X !=
X != all (bag) is equivalent to not in.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
insert into S ({"v": 2}, {"v": 3});
select t.x from T as t where t.x != all (select s.v from S as s);
Result
[
1
]
Match Wins
A real match makes = any true even when the bag holds a null.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
insert into S ({"v": 2}, {"v": null});
select t.x from T as t where t.x = any (select s.v from S as s);
Result
[
2
]
Order Limit
A subquery with order by + limit on the right of in.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2}, {"x": 3});
insert into S ({"v": 3}, {"v": 2}, {"v": 1});
select t.x from T as t where t.x in (select s.v from S as s order by s.v limit 1);
Result
[
1
]
Nested Subquery
A subquery whose predicate contains another subquery.
SQL
create table T;
create table S;
create table U;
insert into T ({"x": 1});
insert into S ({"g": 5});
insert into U ({"w": 5});
select t.x from T as t where exists (select s.g from S as s where s.g in (select u.w from U as u));
Result
[
1
]
Param In Subquery
A query parameter resolves inside a subquery.
SQL
create table T;
create table S;
insert into T ({"x": 2});
insert into S ({"v": 1}, {"v": 2});
select t.x from T as t where t.x in (select s.v from S as s where s.v > ?);
Result
[
2
]
An Outer
An outer aggregate and a scalar subquery with its own aggregate stay at separate query levels — the inner count is not folded into the outer.
SQL
create table T;
create table S;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"v": 1}, {"v": 2}, {"v": 3});
select count(*) as c, (select count(*) from S as s) as d from T as t;
Result
[
{ "c": 2, "d": 3 }
]
Grouped Projection
A scalar subquery as a member of a grouped projection (known gap).
SQL
create table T;
create table S;
insert into T ({"g": "a"}, {"g": "a"}, {"g": "b"});
insert into S ({"v": 1}, {"v": 2});
select t.g as g, count(*) as n, (select count(*) from S as s) as d from T as t group by t.g;
Result
[
{ "g": "a", "n": 2, "d": 2 },
{ "g": "b", "n": 1, "d": 2 }
]
Subquery In Having
A scalar subquery as a having comparand (known gap).
SQL
create table T;
create table S;
insert into T ({"g": "a"}, {"g": "a"}, {"g": "b"});
insert into S ({"v": 1}, {"v": 2});
select t.g as g, count(*) as n from T as t group by t.g having count(*) >= (select count(*) from S as s);
Result
[
{ "g": "a", "n": 2 }
]
An Inner
An inner subquery correlates to the binding of a middle subquery.
SQL
create table T;
create table S;
create table U;
insert into T ({"x": 1}, {"x": 2});
insert into S ({"g": 1});
insert into U ({"w": 1});
select t.x from T as t where exists (select s.g from S as s where s.g = t.x and exists (select u.w from U as u where u.w = s.g));
Result
[
1
]