Commands and Statements Commands and Statements

Command SELECT - SQL Foundation

Retrieves data from one or more tables

Syntax
SELECT [DISTINCT][*|ALL] <ColumnExpression> [AS <cAliasName>] [,...]
       FROM <SelectSource> [AS <cAliasName>] [,...]
       [INNER|LEFT [OUTER]|RIGHT [OUTER]|FULL [OUTER]] JOIN <cTableName>
          ON <JoinExpression> | USING <cFieldName> [, <cFieldName>] ]
       [WHERE <FilterExpression> | <JoinExpression>]
       [GROUP BY <Expression> [HAVING <FilterExpression>]
       [UNION [ALL]|INTERSECT|EXCEPT] <SubSelectExpression>]
       [ORDER BY <Expression> [ASC|DESC] [, <Expression> [ASC|DESC]]]
       [LIMIT <nExpression> [OFFSET <nExpression>]
       [VIA (coSession)]
       [INTO [CURSOR] <cAlias>
       |INTO ARRAY <aResult>
       |INTO OBJECTS <aResult> [CLASS <coClassName>]
       |EVAL <cbExpression> ]
       |INTO VALUE <xResult>
       [INTO TABLE <cResultTableName> [VIA <cResultDbeName>]]
Parameters
[DISTINCT][*|ALL] <ColumnExpression> [AS <cAliasName>] [,...]
This parameter defines the columns of the query result of the SQL SELECT command. The ALL or "*" option selects all fields from the tables used in the query. Alternatively, a comma-separated list of expressions can be specified which each define a column to be included in the result. A suitable name for the resulting column is defined automatically by the system. However, an alternative name can be specified for each column via the AS clause. The DISTINCT option removes duplicate rows from the query result. The DISTINCT option can be used only once in an SQL SELECT command. More details on the column specification can be found in the section "SELECT - SQL SELECT Clause".
FROM <SelectSource> [AS <cAliasName>] [,...] [JOIN <cTableName> ON ...]
The FROM clause specifies the input for the SQL SELECT command. In most cases, one or more tables are used as the input for SQL SELECTs, in which case<SelectSource> specifies a table name. It is also possible, however, to specify expressions or subselects in the FROM clause.
If multiple source tables are defined and no specific join criteria is given in a query, the result is the Cartesian product (cross join) of all the tables specified in the FROM clause. Because query results produced using cross joins can be get quite large, the rows returned should be restricted to a subset of the Cartesian product using the <FilterExpression> and <JoinExpression> parameters. More details about the FROM clause are given in the section "SELECT - SQL FROM Clause"
WHERE <FilterExpression> | <JoinExpression> | <CombinedExpression>
The argument specified for the WHERE clause specifies the criteria rows must meet in order to be included in the query result. This can be a filter defining a subset of the rows, a join expression linking the tables specified in the FROM clause, or a combination of the above. Note that if more than one table is used in a query, a join condition should be specified for each table listed after the first table in the FROM clause. Multiple join conditions must be connected with the AND operator. Each join condition is structured as follows:
<cFieldName1> <Comparison> <cFieldName2>
<FieldName1> is the name of a field in a table, <cFieldName2> is the name of a field in another table, and <comparison> is one of the comparison operators.
GROUP BY <Expression> [HAVING <FilterExpression>]
Groups the rows of the query based on values in one or more columns. <Expression> can be the following: the name of a regular table field, a field that contains a SQL field function, or a numeric expression that specifies the position of the column in the result table (the leftmost column is number 1).
HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in <FilterExpression> must unambiguously reference a grouping column, unless the reference appears within an aggregate function.
The presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. This is the same as a query which contains aggregate functions but no GROUP BY clause. In this case, all selected rows are considered to form a single group. The column specification and HAVING clause of a SQL SELECT can only reference table columns from within aggregate functions, however. Therefore, such a query emits a single row if the HAVING condition is true, and zero rows if it is not true.
<FilterExpression> specifies a filter condition which the groups are required to meet in order to be included in the query result. HAVING should be used with GROUP BY. Any number of filter conditions can be connected using the AND and the OR operators. NOT reverses the value of a logical expression. Filters can not contain a subquery.
A HAVING clause without a GROUP BY clause acts like a WHERE clause. Alias names for columns and field functions can be used in the HAVING clause. If a HAVING clause contains no field functions, a WHERE clause should be used instead for better performance.
[UNION|INTERSECT|EXCEPT] [DISTINCT|ALL] <SubSelectExpression>]
Combines the results of an SQL SELECT command with the results of another SQL SELECT command. The UNION operator returns all rows contained in one or both of the result sets. The INTERSECT operator returns all rows that are contained in both result sets. The EXCEPT operator returns the rows that are in the first result set but not in the second. In all three cases, duplicate rows are eliminated unless ALL is specified. More details on the UNION clause can be found in section "SELECT - SQL UNION Clause".
ORDER BY <Expression> [ASC|DESC] [, <Expression> [ASC|DESC]]
The ORDER BY clause causes the result rows to be sorted according to the specified expression(s). If two rows are equal according to the leftmost expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, the rows are sorted in an implementation-dependent order *).
ASC is the default for ORDER BY and causes the elements of the query result to be sorted in ascending order. DESC specifies a descending order for the query result. If the ORDER BY clause is not specified, the query result is sorted in random order.

*) Dbf tables are sorted the record number.

Local SQL supports any kind of expression in the ORDER BY clause. With remote SQL only field names are allowed.

LIMIT <nValue> [OFFSET <nValue>]
The LIMIT clause is used to place an upper bound on the number of rows returned by a SQL SELECT statement. Any numeric expression can be used in the LIMIT clause. Otherwise, a runtime error results. If the expression evaluates to a negative value, then there is no upper bound on the number of rows returned. Otherwise, only the first N rows are included in the result set, where N is the value returned by the LIMIT expression. If the SQL SELECT command would return less than N rows without a LIMIT clause, then the entire result set is returned.
The expression specified in the optional OFFSET clause must also evaluate to a numeric value. If a LIMIT clause includes an OFFSET clause, then the first M rows are omitted from the result set and the next N rows are returned. M and N are the values that the OFFSET and LIMIT clauses evaluate to, respectively. If the SQL SELECT command would return less than M+N rows without a LIMIT clause, then the first M rows are skipped and the remaining rows (if any) are returned. If the OFFSET clause evaluates to a negative value, the value specified is ignored.
INTO [CURSOR] <cTableName>
By default, the result set is returned in a new workarea. The name of the workarea can be specified using the optional INTO CURSOR] clause. In this case,<cTableName> is a character string which specifies the name of the workarea.
INTO ARRAY <aVariable>
If INTO ARRAY is specified in an SQL SELECT command, the result set of the operation is a multi-dimensional array. Each element contains a sub-array which represents a single row of the result set. <aVariable> is the name of a variable the array with the result set is assigned to.
INTO OBJECTS <aVar> [CLASS <coExpression>]
The result set of the SQL SELECT command is represented via an array of objects. By default, the objects in the array are instances of the class DataObject. This default can be overriden by specifying a class object in the argument of the optional CLASS clause. In this case, the array returned contains objects of the class specified in <coExpression>. <aVar> is the name of a variable the array with the result set is assigned to.
The values in each row of the result set are assigned to the member variables of an object in the result array. The member variables must have the same name as the respective column in the result set. If such a member variable does not exist and the object is derived from DataObject class, the corresponding member variable is created. If the object is an instance of a static class, however, the respective column in the result set is ignored.
INTO TABLE <cTableName>
Stores the query results in a table. If the specified table exists already, it is overwritten without warning. The table remains open and active after the execution of the SQL SELECT command. The storage format of the table is determined by the current Database Engine.
EVAL <cbExpression>
Evaluates the code block specified in <cbExpression> for each row of the result set. The values in the columns of the result set are passed to the code block as parameters in the same order as the columns appear in the column specification of the SQL SELECT command. The result set is discarded after all rows were processed.
VIA (<oSession>)
<oSession> optionally specifies the session context for the operation. <oSession> is an object of DacSession() or any derived class and must be specified as an object expression in parentheses. If <oSession> is not specified, the SQL SELECT statement is executed by the local SQL engine. If a VIA clause is specified in an SQL statement on the other hand, the statement executes in the context defined by the session which in most cases involves a remote backend.
Description

The SQL SELECT command retrieves rows from one or more tables. The general processing performed by SQL SELECT is as follows:

1.) All elements in the FROM list are computed. Each element in the FROM list is a real or virtual table. If more than one element is specified in the FROM list, they are cross-joined together.

2.) If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output.

3.) If the GROUP BY clause is specified, the output is combined into groups of rows that match on one or more values. If the HAVING clause is present, it eliminates groups that do not satisfy the given condition.

4.) The actual output rows are computed using the SELECT output expressions for each selected row or row group.

5.) SELECT DISTINCT eliminates duplicate rows from the result. SELECT ALL (the default) returns all candidate rows, including duplicates.

6.) Using the operators UNION, INTERSECT, and EXCEPT, the output of more than one SELECT statement can be combined to form a single result set.

7.) If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

8.) If the LIMIT or OFFSET clause is specified, the SELECT statement only returns a subset of the result rows.

Feedback

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.