From
The From clause introduces bindings by iterating data sources. Each source binds rows under an alias for use in later clauses. Multiple comma-separated sources form a lateral cross product: each subsequent source is evaluated in the context of bindings from preceding sources.
Syntax
Railroad
BNF
from-clause ::= "from" source ( "," source )*
source ::= table-ref
| path-expr
| "(" select-stmt ")"
| unpivot-source
table-ref ::= identifier [ "as" identifier ]
unpivot-source ::= "unpivot" expr "as" identifier [ "at" identifier ]
Rules
- Each source must have an alias; when
asis omitted, the alias defaults to the table name. (phase: evaluate first in the query pipeline) - Sources are evaluated left to right; the right-hand source may reference bindings from the left (lateral evaluation). (phase: evaluate first)
- A source may be a table name, a path expression rooted at a bound name, a parenthesized subquery, or
unpivot. - Scanning an empty table yields no rows; insertion order is preserved for keyless tables. (phase: evaluate first)
Examples
Minimal
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 } }
]
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
]
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
[]
Compound
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
[]
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
[]
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 }
]
Error cases
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
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