Chapter 8. SQL for Oracle NoSQL Database Queries

Table of Contents

Select-From-Where (SFW) Expressions
SELECT Clause
FROM Clause
WHERE Clause
ORDER BY Clause
OFFSET Clause
LIMIT Clause

This chapter describes the Select-From-Where (SFW) expression, which is the core expression used to form SQL queries. For examples of using SFW expressions, see these extended examples:

For a more detailed description of the language see the SQL for Oracle NoSQL Database Specification.

Note

The examples shown in this chapter rely on the sample data shown in Example Data.

Select-From-Where (SFW) Expressions

A query is always a single Select-From-Where (SFW) expression. The SFW expression is essentially a simplified version of the SQL Select-From-Where query block. The two most important simplifications are the lack of support for joins and for subqueries. On the other hand, to manipulate complex data (records, arrays, and maps), SQL for Oracle NoSQL Database provides extensions to traditional SQL through novel kinds of expressions, such as path expressions.

The semantics of the SFW expression are similar to those in standard SQL. Processing starts with the FROM clause, followed by the WHERE clause (if any), followed by the ORDER BY clause (if any), followed by the OFFSET and LIMIT clauses, and finishing with the SELECT clause. Each clause is described below. A query must contain exactly one SFW expression, which is also the top-level expression of the query. Subqueries are not supported yet.

SELECT <expression>
FROM <table name>
[WHERE <expression>]
[ORDER BY <expression> [<sort order>]]
[OFFSET <number>]
[LIMIT <number>]; 

Each of the SFW clauses are introduced in the following sections. For details on each clause, see the SQL for Oracle NoSQL Database Specification.

SELECT Clause

SELECT clauses come in two forms. In the first form, it contains only a single star (*) symbol. This form simply returns all rows.

SELECT * FROM Users;

In the second form, the SELECT clause contains a comma-separated list of field expressions, where each expression is optionally associated with a name. In the simplest case, each expression is simply the name of a column in the table from which data is being selected.

SELECT id, firstname, lastname FROM Users;

The AS keyword can also be used:

SELECT id, firstname AS Name, lastname AS Surname FROM Users;

SELECT clauses can contain many different kinds of expressions. For more information, see Expressions.

The SELECT clause always returns a record. Normally, the record has one field for each field expression, and the fields are arranged in the same order as the field expressions. Each field value is the value computed by the corresponding field expression and its name is the name associated with the field expression. If no field name is provided explicitly (using the AS keyword), one is automatically generated for you.

To create valid records, the field names must be unique, and they must return at most one item. If a field expression returns more than one result, the result is returned in an array.

If the result of a field expression is empty, NULL is used as the value of the corresponding field in the record returned by SELECT.

Note

If the SELECT clause contains only one field expression with no associated name, then just the value returned by the clause is returned. If this value is already a record, then this is returned. If this value is not a record, then it is wrapped in a record before being returned.

SELECT Clause Hints

The SELECT clause can contain one or more hints which are used to help choose an index to use for the query. A hint is a comment that begin with a + symbol:

/*+ <hint> */

Each hint takes the form:

<hint type> (<table path> [<index name>]) [comment string]

The following hint types are supported:

  • FORCE_INDEX

    Specifies a single index, which is used without considering any of other indexes. This is true even if there are no index predicates for the forced index. However, if the query has an ORDER BY clause, and the forced index is not the sorting index, an error is thrown.

    This index hint requires you to specify an <index name>.

  • PREFER_INDEXES

    The PREFER_INDEXES hint specifies one or more indexes. The query processor may or may not use one of the preferred indexes.

    This index hint requires you to specify at least one <index name>.

  • FORCE_PRIMARY_INDEX

    Requires the query to use the table's primary index.

    You do not specify an <index name> when you use this type of hint.

  • PREFER_PRIMARY_INDEX

    Specifies that you prefer to use the primary index for the query. This index may or may not be used.

    You do not specify an <index name> when you use this type of hint.

For more information on indexes, see Working With Indexes.

FROM Clause

The FROM clause is very simple: it can include only a single table. The table is specified by its name, which may be a composite (dot-separated) name in the case of child tables. The table name may be followed by a table alias.

For example, to select a table named Users:

<select expression> FROM Users <other clauses>;

To select a table named People, which is a child of a table named Organizations:

<select expression> FROM Organizations.People <other clauses>;

To select a table named People and give it the alias u:

<select expression> FROM Users u <other clauses>;

The result of the FROM clause is a sequence containing the rows of the referenced table. The FROM clause creates a nested scope, which exists for the rest of the SFW expression.

The SELECT, WHERE, and ORDER BY clauses operate on the rows produced by the FROM clause, processing one row at a time. The row currently being processed is called the context row. The context row can be referenced in expressions by either the table name, or the table alias.

If the table alias starts with a dollar sign ($), then it serves as a variable declaration whose name is the alias. This variable is bound to the context row and can be referenced within the SFW expression anywhere an expression returning a single record may be used. If this variable has the same name as an external variable, it hides the external variable. Because table alias are essentially variables, the like all other variables their names are case-sensitive.

WHERE Clause

The WHERE clause returns a subset of the rows coming from the FROM clause. Specifically, for each context row, the expression in the WHERE clause is evaluated. The result of this expression must have type BOOLEAN?. If the result is false, or empty, or NULL, the row is skipped; otherwise the row is passed on to the next clause.

For example, to limit the rows selected to just those where the column firstname contains John:

<select statement> <from statement> WHERE firstname = "John";

ORDER BY Clause

The ORDER BY clause reorders the sequence of rows it receives as input. The relative order between any two input rows is determined by evaluating, for each row, the expressions listed in the order-by clause and comparing the resulting values. Each order-by expression must have type AnyAtomic?.

Note

It is possible to perform ordering only if there is an index that already sorts the rows in the desired order.

For detailed information on how comparison is performed for order-by expressions, see the SQL for Oracle NoSQL Database Specification.

For example, to order a query result by age.

<select statement> <from statement> WHERE firstname = "John" 
ORDER BY age;

It is possible to specify a sorting order: ASC (ascending) or DESC (descending). Ascending is the default sorting order. To present these results in descending order:

<select statement> <from statement> WHERE firstname = "John" 
ORDER BY age DESC;

You can also specify whether NULLS should come first or last in the sorting order. For example:

<select statement> <from statement> WHERE firstname = "John" 
ORDER BY age DESC NULLS FIRST;

Remember that ordering is only possible if there is an index that sorts the rows in the desired order. Be aware that, in the current implementation, NULLs are always sorted last in the index. The specified handling for NULLs must match the index so, currently, if the sort order is ascending then NULL LAST must be used, and if the sort order is descending then NULL FIRST must be used.

Comparison Rules

This section describes the sorting rules used when query results are sorted.

First, consider the case where only one ORDER BY clause is used in the query.

Two rows are considered equal if both rows contain the same number of elements, and for equivalent positions in each row, the atomic values are identical. So if you have two rows, R1 and R2, then they are equal if R1[0] = R2[0] and R1[1] = R1[1]. In this context, NULLs are considered equal only to other NULLs.

Assuming that the number of elements in R1 and R2 are equal, then R1 is less than R2 if any of the following is true:

  • No NULLs appear in either row and sorting is in ascending order. In this case, R1 is less than R2 if there are a positionally-equivalent pair of atomic elements (as evaluated from lowest to highest) where the R1 element is less than the R2 element. That is, if R1[1] < R2[2] then R1 is less than R2.

  • No NULLs appear in either row and sorting is in descending order. In this case, R1 is less than R2 if there are a positionally-equivalent pair of atomic elements (as evaluated from lowest to highest) where the R1 element is greater than the R2 element. That is, if R1[1] > R2[2] then R1 is less than R2.

  • A NULL appears in R2, but not in R1, and sorting is in ascending order with NULLS LAST.

  • A NULL appears in R2, but not in R1, and sorting is in descending order with NULLS FIRST.

If multiple ORDER BY statements are offered, then atomic values are returned for comparison purposes by evaluating the statements from left to right.

Be aware that if an expression returns an empty sequence, then the return value is NULL.

If no sorting order is provided to the query, then by default ascending order with NULLS LAST is used. If only the sort order is specified, then NULLs sort last if the order is ascending. Otherwise, they sort first.