SELECT Clause

The SELECT clause transforms each input row to a new record that will appear in the query result. The SELECT clause comes in two forms: "select star" form and "projection" form.

select star form: In select star form the SELECT clause contains a single star symbol (*). In this the SELECT clause is a no-op; it simply returns its input sequence of rows.
projection form: In the projection form the SELECT clause contains a list of expressions, where each expression is optionally associated with a name. In this the listed expressions and their associated names are refered as field expressions and field names respectively.

Syntax

select_clause ::= SELECT [DISTINCT] select_list

select_list ::= [hints] 
   (STAR | (expression AS id ("," expression AS id)*))

Semantics

In projection form, the SELECT clause creates a new record for each input row. In this the record constructed by the SELECT clause has one field for each field expression and the fields are arranged in the same order as the field expressions. For each field, its value is the value computed by the corresponding field expression and its name is the name specified by the AS keyword, or if no field name is provided explicitly (via the AS keyword), one is generated internally during query compilation. To create valid records, the field names must be unique. Furthermore, each field value must be exactly one item. To achieve this, the following two implicit conversions are employed:

If the result of a field expression is empty, NULL is used as the value of the corresponding field in the created record.
If the compiler determines that a field expression may return more than one item, it wraps the field expression with a conditional array constructor. See the Array and Map Constructors section. During runtime, an array will be constructed only if the field expression does actually return more than one item; if so, the returned items will be inserted into the constructed array, which will then be used as the value of the corresponding field in the created record.

The above semantics imply that all records generated by a SELECT clause have the same number of fields and the same field names. As a result, a record type can be created during compilation time that includes all the records in the result set of a query. This record type is the type associated with each created record, and is available programmatically to the application.

The SELECT clause can contain an optional DISTINCT keyword. If the SELECT clause contains the DISTINCT keyword, then the database will return only one copy of each set of duplicate rows selected. Duplicate rows are those with matching values in the SELECT list. The query uses the combination of values in all specified columns in the SELECT list to evaluate the uniqueness. See the Example 6-19 example. Equality between values is checked using the semantics of the "=" operator. See the Value Comparison Operators section.

If the SELECT expression is a grouping one, then the expressions in the SELECT list must be the grouping expressions (in the GROUP BY clause, if any), or aggregate functions, or expressions that are built on top of grouping expression and/or aggregate functions.

Note:

The SELECT clause may also contain one or more hints, that help the query processor choose an index to use for the query. See the Choosing the Best Applicable Index section.

Example 6-14 SELECT Clause

SELECT * FROM users;

Example 6-15 SELECT Clause

Select the id and the last name for users whose age is greater than 30. We show 4 different ways of writing this query, illustrating the different ways that the top-level columns of a table may be accessed.

SELECT id, lastName FROM users WHERE age > 30;

SELECT users.id, lastName FROM users WHERE users.age > 30;

SELECT $u.id, lastName FROM users $u WHERE $u.age > 30;

SELECT u.id, lastName FROM users u WHERE users.age > 30;

Example 6-16 SELECT Clause

Select the id and the last name for users whose age is greater than 30, returning the results sorted by id. Sorting is possible in this case because id is the primary key of the users table.

SELECT id, lastName FROM users WHERE age > 30 ORDER BY id;

Example 6-17 SELECT Clause

Select the list of distinct age of the users.

SELECT DISTINCT age FROM users;
 
{"age":25}
{"age":43}
{"age":22}

Example 6-18 SELECT Clause

Select the list of othernames of the users. Notice that the output of the SELECT command is compared and any duplicates are removed from the final output.

SELECT otherNames FROM Users;
 
{"otherNames":null}
{"otherNames":null}
{"otherNames":[{"first":"Johny","last":"BeGood"}]}
 
SELECT DISTINCT otherNames FROM Users;

{"otherNames":null}
{"otherNames":[{"first":"Johny","last":"BeGood"}]}

Example 6-19 SELECT Clause

Select the list of firstname and othernames of the users. Notice that the query uses the combination of values in all specified columns in the SELECT list to evaluate the uniqueness.

SELECT firstName, otherNames FROM Users;
 
{"firstName":"Peter","otherNames":null}
{"firstName":"Mary","otherNames":null}
{"firstName":"John","otherNames":[{"first":"Johny","last":"BeGood"}]}
  
SELECT DISTINCT firstName, otherNames FROM Users;
 
{"firstName":"Peter","otherNames":null}
{"firstName":"Mary","otherNames":null}
{"firstName":"John","otherNames":[{"first":"Johny","last":"BeGood"}]}