SELECT
–retrieve or compute a set of values.
[ WITH with-item [, ...] ]
SELECT expr
[ FILTER filter-expr ]
[ ORDER BY order-expr [direction] [THEN ...] ]
[ OFFSET offset-expr ]
[ LIMIT limit-expr ] ;
The optional FILTER
clause, where filter-expr
is any expression that has a result of type bool
.
The condition is evaluated for every element in the set produced by
the SELECT
clause. The result of the evaluation of the
FILTER
clause is a set of boolean values. If at least one value
in this set is true
, the input element is included, otherwise
it is eliminated from the output.
The optional ORDER BY
clause has this general form:
ORDER BY
order-expr [ ASC | DESC ] [ EMPTY { FIRST | LAST } ]
[ THEN ... ]
The ORDER BY
clause produces a result set sorted according
to the specified expression or expressions, which are evaluated
for every element of the input set.
If two elements are equal according to the leftmost expression, they are compared according to the next expression and so on. If two elements are equal according to all expressions, the resulting order is undefined.
Each expression can be an arbitrary expression that results in a value of an orderable type. Primitive types are orderable, object types are not. Additionally, the result of each expression must be an empty set or a singleton. Using an expression that may produce more elements is a compile-time error.
An optional ASC
or DESC
keyword can be added after any
expression. If not specified ASC
is assumed by default.
If EMPTY LAST
is specified, then input values that produce
an empty set when evaluating an expression are sorted after
all other values; if EMPTY FIRST
is specified, then they
are sorted before all other values. If neither is specified,
EMPTY FIRST
is assumed when ASC
is specified or implied,
and EMPTY LAST
when DESC
is specified.
The optional OFFSET
clause, where
offset-expr
is a singleton expression of an integer type.
This expression is evaluated once and its result is used
to skip the first element-count elements of the input set
while producing the output. If element-count evaluates to
an empty set, it is equivalent to OFFSET 0
, which is equivalent
to omitting the OFFSET
clause. If element-count evaluates
to a value that is larger then the cardinality of the input set,
an empty set is produced as the result.
The optional LIMIT
clause, where limit-expr
is a singleton expression of an integer
type. This expression is evaluated once and its result is used
to include only the first element-count elements of the input set
while producing the output. If element-count evaluates to
an empty set, it is equivalent to specifying no LIMIT
clause.
SELECT
retrieves or computes a set of values. The data
flow of a SELECT
block can be conceptualized like this:
WITH MODULE example
select clause
SELECT
expr compute a set of things
optional clause
FILTER
expr filter the computed set
optional clause
ORDER BY
expr define ordering of the filtered set
optional clause
OFFSET
expr slice the filtered/ordered set
optional clause
LIMIT
expr slice the filtered/ordered set
Please note that the ORDER BY
clause defines ordering that can
only be relied upon if the resulting set is not used in any other
operation. SELECT
, OFFSET
and LIMIT
clauses are the only
exception to that rule as they preserve the inherent ordering of the
underlying set.
The first clause is SELECT
. It indicates that FILTER
, ORDER
BY
, OFFSET
, or LIMIT
clauses may follow an expression, i.e.
it makes an expression into a SELECT
statement. Without any of the
optional clauses a (SELECT Expr)
is completely equivalent to
Expr
for any expression Expr
.
Consider an example using the FILTER
optional clause:
WITH MODULE example
SELECT User {
name,
owned := (SELECT
User.<owner[IS Issue] {
number,
body
}
)
}
FILTER User.name LIKE 'Alice%';
The above example retrieves a single user with a specific name. The
fact that there is only one such user is a detail that can be well-
known and important to the creator of the database, but otherwise non-
obvious. However, forcing the cardinality to be at most 1 by using the
LIMIT
clause ensures that a set with a single object or
{}
is returned. This way any further code that relies on the
result of this query can safely assume there’s only one result
available.
WITH MODULE example
SELECT User {
name,
owned := (SELECT
User.<owner[IS Issue] {
number,
body
}
)
}
FILTER User.name LIKE 'Alice%'
LIMIT 1;
Next example makes use of ORDER BY
and LIMIT
clauses:
WITH MODULE example
SELECT Issue {
number,
body,
due_date
}
FILTER
EXISTS Issue.due_date
AND
Issue.status.name = 'Open'
ORDER BY
Issue.due_date
LIMIT 3;
The above query retrieves the top 3 open Issues with the closest due date.
The FILTER
clause cannot affect anything aggregate-like in the
preceding SELECT
clause. This is due to how FILTER
clause
works. It can be conceptualized as a function like filter($input,
SET OF $cond)
, where the $input
represents the value of the
preceding clause, while the $cond
represents the filtering
condition expression. Consider the following:
WITH MODULE example
SELECT count(User)
FILTER User.name LIKE 'Alice%';
The above can be conceptualized as:
WITH MODULE example
SELECT _filter(
count(User),
User.name LIKE 'Alice%'
);
In this form it is more apparent that User
is a SET OF
argument (of count()
), while User.name LIKE 'Alice%'
is
also a SET OF
argument (of filter
). So the symbol User
in
these two expressions exists in 2 parallel scopes. Contrast it with:
# This will actually only count users whose name starts with
# 'Alice'.
WITH MODULE example
SELECT count(
(SELECT User
FILTER User.name LIKE 'Alice%')
);
# which can be represented as:
WITH MODULE example
SELECT count(
_filter(User,
User.name LIKE 'Alice%')
);