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[0] ...
| call-expression // uppercase(x) ...
| sql-expression // SQL expressions and special forms
| value-constructor
| value-literalSFW 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.
SELECT Clauses
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' symbolSELECT
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.
SELECT VALUE
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
FROM 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' expressionSingle-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.
See Joins
WHERE Clause
where-clause ::= 'WHERE' expressionThe 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-clause ::= 'HAVING' expressionThe 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-whereIn addition to the SQL set operators, PartiQL provides OUTER UNION, 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 # identityORDER 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
PIVOT
The PIVOT Clause is a special PartiQL SELECT Clause which outputs a single tuple from a collection of bindings.
See PIVOT Clause