PartiQL Query Basics
A PartiQL query is either a SELECT-FROM-WHERE (SFW) statement or a value expression. Unlike SQL expressions, which are restricted to outputting scalar and null values, PartiQL expressions output arbitrary PartiQL values, i.e. — scalars, absent, and complex values. As a result, PartiQL SFW expressions are fully composable within other SFW expressions as well as value expressions. Indeed, PartiQL allows the top-level query to also be a value expression, not just a SELECT-FROM-WHERE as in SQL.
query ::= select-from-where | expression expression ::= '(' select-from-where ')' | value-expression select-from-where ::= select-clause let-clause? from-clause // SELECT ... FROM where-clause? // WHERE group-by-clause? // GROUP BY having-clause? // HAVING set-op-clause? // UNION|INTERSECT|EXCEPT order-by-clause? // ORDER BY fetch-clause? // LIMIT ... OFFSET value-expression ::= path-expression // x.a ... | call-expression // uppercase(x) ... | sql-expression // SQL expressions and special forms | value-constructor | value-literal
SFW queries are composed of several relation expressions derived from its clauses. Each clause (except for SELECT VALUE and PIVOT) can be understood in isolation from the other clauses because a clause is simply a function that inputs and outputs collections of binding tuples.
Core PartiQL SFW queries have a SELECT VALUE clause (in lieu of SQL’s SELECT clause) that can create outputs that are collections of anything — i.e. collections of tuples, collections of scalars, collections of arrays, collections of mixed type elements.
select-clause ::= 'SELECT' set-quantifier? '*' // SELECT * | 'SELECT' set-quantifier? binding-list // SELECT | 'SELECT' set-quantifier? VALUE expression // SELECT VALUE | 'PIVOT' expression AT expression // PIVOT binding-list ::= binding (',' binding)* binding ::= expression 'AS' symbol
SQL’s well-known SELECT clause can be described as syntactic sugar over SELECT VALUE. When a SFW query is used as a subquery it is coerced into a scalar or a tuple, in the ways that SQL coerces the results of subqueries.
The SELECT VALUE clause inputs a bag of binding tuples or an array of binding tuples (from the other clauses of the SQL query) and outputs a bag or an array. For example, if the query only has SELECT VALUE, FROM, and WHERE clauses, then the bindings that are output by the WHERE clause are input by SELECT VALUE the clause. Unlike SQL, the output of a SELECT VALUE clause need not be a bag or array of tuples. It is a bag or array of any kind of PartiQL values. For example, it may be a bag of integers, or a bag of arrays, etc. Indeed, the values may be heterogeneous. For example, the output may even be a bag that has both integers and arrays.
See SELECT Clause
The FROM Clause is responsible for producing a collection of binding tuples from either a single collection or multiple collections via joins. The syntax is as follows:
from-clause ::= 'FROM' collection collection ::= collection ',' collection // CROSS JOIN | collection join-type 'CROSS' 'JOIN' collection // CROSS JOIN | collection join-type 'JOIN' collection join-condition // JOIN ... ON | collection-non-join | '(' collection ')' collection-non-join ::= expression symbol // Foo f | collection-expression // Foo AS x AT y | 'UNPIVOT' collection-expression // UNPIVOT Foo as v AT k join-type ::= INNER | LEFT | RIGHT | FULL join-condition ::= 'ON' expression
Single-Item FROM Clause
The single-item FROM clause simply ranges over a collection value.
See FROM Clause
Multi-Item FROM Clause
The multi-item FROM clause is responsible for joining the tuples of multiple relations. The join type can be one of CROSS, LEFT, RIGHT, INNER, OR FULL. All joins except for CROSS JOIN can have a join condition.
Unlike SQL, the expressions of the right-hand-side of a PartiQL CROSS JOIN may use fields of tuples in the left-hand-side. This enables descending into nested data structures and functions like nested loops.
where-clause ::= 'WHERE' expression
The WHERE clause inputs the bindings that have been produced from the FROM clause and outputs the ones that satisfy its condition. The boolean predicates follow SQL’s 3-valued logic.
See WHERE Clause
GROUP BY Clause
group-by-clause ::= 'GROUP BY' binding-list ('AS' symbol)?
The PartiQL GROUP BY clause expands SQL’s grouping. Unlike SQL, the PartiQL GROUP BY can be thought of as a standalone operator that inputs a collection of binding tuples and outputs a collection of binding tuples. A GROUP BY Clause has a binding list which is list of aggregate expressions ei and an associate binding name ni. Additionally, the GROUP BY Clause may contain a grouping variable g known as the group as alias.
See GROUP BY Clause
having-clause ::= 'HAVING' expression
The HAVING Clause is effectively the same as the WHERE Clause; however, the HAVING Clause is applied to the aggregate values output by a GROUP BY Clauses. SQL allows for aggregate expressions in the HAVING Clause, but that behavior is currently not supported by PartiQL.
See HAVING Clause
UNION / INTERSECT / EXCEPT
set-op-clause ::= 'UNION' 'ALL'? select-from-where | 'INTERSECT' 'ALL'? select-from-where | 'EXCEPT' 'ALL'? select-from-where
In addition to the SQL set operators, PartiQL provides
OUTER INTERSECT, and
OUTER EXCEPT which enable combining arbitrary values by coercing to bags. For example,
<< 1 >> OUTER UNION 2 results in
<< 1, 2 >>.
Each bag operator has the form
LHS Opbag RHS where LHS and RHS are SFW queries and Opbag is the bag (or set) operator. Additionally, the operator may be suffixed with ALL which indicates the output may have duplicate elements. In its absence, DISTINCT is implicit and duplicates are eliminated from the final result.
The standard SQL bag operators are defined as:
LHS UNION ALL RHS = MULTISET_UNION(LHS, RHS) LHS INTERSECT ALL RHS = MULTISET_INTERSECT(LHS, RHS) LHS EXCEPT ALL RHS = MULTISET_DIFFERENCE(LHS, RHS) LHS UNION DISTINCT RHS = DISTINCT(MULTISET_UNION(LHS, RHS)) LHS INTERSECT DISTINCT RHS = DISTINCT(MULTISET_INTERSECT(LHS, RHS)) LHS EXCEPT DISTINCT RHS = DISTINCT(MULTISET_DIFFERENCE(LHS, RHS))
Let V1 and V2 be arbitrary values, and let C be a function which coerces a value to a bag. The OUTER operators are defined as
V1 OUTER UNION ALL V2 = MULTISET_UNION(C(V1), C(V2)) V1 OUTER INTERSECT ALL V2 = MULTISET_INTERSECT(C(V1), C(V2)) V1 OUTER EXCEPT ALL V2 = MULTISET_DIFFERENCE(C(V1), C(V2)) V1 OUTER UNION DISTINCT V2 = DISTINCT(MULTISET_UNION(C(V1), C(V2))) V1 OUTER INTERSECT DISTINCT V2 = DISTINCT(MULTISET_INTERSECT(C(V1), C(V2))) V1 OUTER EXCEPT DISTINCT V2 = DISTINCT(MULTISET_DIFFERENCE(C(V1), C(V2)))
The coercion function C is defined for all PartiQL Values by:
C(absent_value) -> << >> C(scalar_value) -> << scalar_value >> # singleton bag C(tuple_value) -> << tuple_value >> # singleton bag, see future extensions C(array_value) -> bag_value # discard ordering C(bag_value) -> bag_value # identity
ORDER BY Clause
order-by-clause ::= 'ORDER' 'BY' sort-spec-list sort-spec-list ::= sort-spec (',' sort-spec)* sort-spec ::= expression ('ASC'|'DESC')? ('NULLS' (FIRST|LAST))?
The ORDER BY Clause is responsible for turning its input bag into an array. In the following aspects, PartiQL extends the SQL semantics to resolve issues that are not relevant in SQL but emerge when working on Ion data.
See ORDER BY Clause.
LIMIT … OFFSET
fetch-clause ::= 'LIMIT' expression ('OFFSET' expression)?
The LIMIT … OFFSET clauses take the form
LIMIT <N> OFFSET <M> and returns the first N binding tuples of its input collection, skipping the first M if an OFFSET is specified.
PartiQL allows specifying an OFFSET value without a LIMIT value.
See LIMIT … OFFSET
The PIVOT Clause is a special PartiQL SELECT Clause which outputs a single tuple from a collection of bindings.
See PIVOT Clause