SELECT Statement

Quick Links:

Syntax

SELECT [ ALL | DISTINCT ] [ row-limitation-option1 ] select-list
   … [ INTO { <host-variable-list> | <variable-list> | <table-name >} ]
   … [ INTO LOCAL TEMPORARY TABLE { <table-name> } ]
   … [ FROM <table-list> ]
   … [ WHERE <search-condition> ]
   … [ GROUP BY [ <expression> [, ...]
         | ROLLUP ( <expression> [, ...] )
         | CUBE ( <expression> [, ...] ) ] ] 
   … [ HAVING <search-condition> ]
   … [ ORDER BY { <expression> | <integer> } [ ASC | DESC ] [, ...] ]
   | [ FOR JSON <json-mode> ] 
   … [ row-limitation-option ]

select-list - (back to Syntax)
   { <column-name>
   | <expression> [ [ AS ] <alias-name> ]
   | * }

row-limitation-option1 - (back to Syntax) 
   FIRST 
   | TOP {ALL | limit-expression} [START AT startat-expression ]
  
limit-expression - (back to row-limitation-option1) or (back to row-limitation-option2)
    simple-expression
  
startat-expression - (back to row-limitation-option1) 
    simple-expression

row-limitation-option2 - (back to Syntax)
   LIMIT { [ offset-expression, ] limit-expression 
   | limit-expression OFFSET offset-expression }

offset-expression - (back to row-limitation-option2)
   simple-expression

simple-expression - (back to startat-expression) or (back to offset-expression) or (back to limit-expression)
   <integer>
   | <variable>
   | ( <simple-expression> )
   | ( <simple-expression> { + | - | * } <simple-expression> )

Parameters

(back to top)

ALL or DISTINCT
filters query results. If neither is specified, all rows that satisfy the clauses of the SELECT statement are retrieved. If DISTINCT is specified, duplicate output rows are eliminated. This is called the projection of the result of the statement. In many cases, statements take significantly longer to execute when DISTINCT is specified, so reserve the use of DISTINCT for cases where it is necessary.

If DISTINCT is used, the statement cannot contain an aggregate function with a DISTINCT parameter.
row-limitation-option1
specifies the number of rows returned from a query. FIRST returns the first row selected from the query. TOP returns the specified number of rows from the query where <number-of-rows> is in the range 1 – 2147483647 and can be an integer constant or integer variable.
Note You cannot use TOP and LIMIT in the same query.

FIRST and TOP are used primarily with the ORDER BY clause. If you use these keywords without an ORDER BY clause, the result might vary from run to run of the same query, as the optimizer might choose a different query plan.

FIRST and TOP are permitted only in the top-level SELECT of a query, so they cannot be used in derived tables or view definitions. Using FIRST or TOP in a view definition might result in the keyword being ignored when a query is run on the view.

Using FIRST is the same as setting the ROW_COUNT database option to 1. Using TOP is the same as setting the ROW_COUNT option to the same number of rows. If both TOP and ROW_COUNT are set, then the value of TOP takes precedence.

The ROW_COUNT option could produce inconsistent results when used in a query involving global variables, system functions or proxy tables. See ROW_COUNT Option for details.
<select-list>
is a comma delimited list of expressions that specify what is retrieved from the database. If an asterisk (*) is specified, all columns of all tables in the FROM clause (table-name all columns of the named table) are selected. Aggregate functions and analytical functions are allowed in the <select-list>.

Note
In SAP IQ, scalar subqueries (nested selects) are allowed in the select list of the top level SELECT, as in SAP SQL Anywhere and SAP ASE. Subqueries cannot be used inside a conditional value expression (for example, in a CASE statement).

Subqueries can also be used in a WHERE or HAVING clause predicate (one of the supported predicate types). However, inside the WHERE or HAVING clause, subqueries cannot be used inside a value expression or inside a CONTAINS or LIKE predicate. Subqueries are not allowed in the ON clause of outer joins or in the GROUP BY clause.
<alias-names>
can be used throughout the query to represent the aliased expression. Alias names are also displayed by Interactive SQL at the top of each column of output from the SELECT statement. If the optional <alias-name> is not specified after an expression, Interactive SQL displays the expression. If you use the same name or expression for a column alias as the column name, the name is processed as an aliased column, not a table column name.
INTO <host-variable-list>
specifies where the results of the SELECT statement goes. There must be one <host-variable> item for each item in the <select-list>. Select list items are put into the host variables in order. An indicator host variable is also allowed with each <host-variable> so the program can tell if the select list item was NULL. Used in Embedded SQL only.
INTO <variable-list>
specifies where the results of the SELECT statement go. There must be one variable for each item in the select list. Select list items are put into the variables in order. Used in procedures only
INTO <table-name>
creates a table and fills the table with data.

If the table name starts with #, the table is created as a temporary table. Otherwise, the table is created as a permanent base table. For permanent tables to be created, the query must satisfy these conditions:
- The <select-list> contains more than one item, and the INTO target is a single <table-name> identifier, or
- The select-list contains a * and the INTO target is specified as <owner.table>.
To create a permanent table with one column, the table name must be specified as <owner.table>. Omit the owner specification for a temporary table.

This statement causes a COMMIT before execution as a side effect of creating the table. Requires the CREATE TABLE system privilege to execute this statement. No permissions are granted on the new table: the statement is a short form for CREATE TABLE followed by INSERT... SELECT.

A SELECT INTO from a stored procedure or function is not permitted, as SELECT INTO is an atomic statement and you cannot do COMMIT, ROLLBACK, or some ROLLBACK TO SAVEPOINT statements in an atomic statement.

Tables created using this statement do not have a primary key defined. You can add a primary key using ALTER TABLE. A primary key should be added before applying any updates or deletes to the table; otherwise, these operations result in all column values being logged in the transaction log for the affected rows.

Use of this clause is restricted to valid SAP SQL Anywhere queries. SAP IQ extensions are not supported.
INTO LOCAL TEMPORARY TABLE creates a local, temporary table and populates it with the results of the query. When you use this clause, you do not need to start the temporary table name with #.
FROM <table-list>
retrieves rows and views specified in the <table-list>. Joins can be specified using join operators. For more information, see FROM Clause. A SELECT statement with no FROM clause can be used to display the values of expressions not derived from tables. For example:
```
SELECT @@version
```
displays the value of the global variable @@version. This is equivalent to:
```
SELECT @@version
FROM DUMMY
```
Note
If you omit the FROM clause, or if all tables in the query are in the SYSTEM dbspace, the query is processed by SAP SQL Anywhere instead of SAP IQ and might behave differently, especially with respect to syntactic and semantic restrictions and the effects of option settings.

If you have a query that does not require a FROM clause, you can force the query to be processed by SAP IQ by adding the clause “FROM iq_dummy,” where iq_dummy is a one-row, one-column table that you create in your database.
WHERE <search-condition>
specifies which rows are selected from the tables named in the FROM clause. It is also used to do joins between multiple tables. This is accomplished by putting a condition in the WHERE clause that relates a column or group of columns from one table with a column or group of columns from another table. Both tables must be listed in the FROM clause.

The use of the same CASE statement is not allowed in both the SELECT and the WHERE clause of a grouped query.

SAP IQ also supports the disjunction of subquery predicates. Each subquery can appear within the WHERE or HAVING clause with other predicates and can be combined using the AND or OR operators.
GROUP BY
groups columns, alias names, or functions. GROUP BY expressions must also appear in the select list. The result of the query contains one row for each distinct set of values in the named columns, aliases, or functions. The resulting rows are often referred to as groups since there is one row in the result for each group of rows from the table list. In the case of GROUP BY, all NULL values are treated as identical. Aggregate functions can then be applied to these groups to get meaningful results.

GROUP BY must contain more than a single constant. You do not need to add constants to the GROUP BY clause to select the constants in grouped queries. If the GROUP BY expression contains only a single constant, an error is returned and the query is rejected.

When GROUP BY is used, the select list, HAVING clause, and ORDER BY clause cannot reference any identifiers except those named in the GROUP BY clause. This exception applies: The <select-list> and HAVING clause may contain aggregate functions.

ROLLUP operator

subtotals GROUP BY expressions that roll up from a detailed level to a grand total.

The ROLLUP operator requires an ordered list of grouping expressions to be supplied as arguments. ROLLUP first calculates the standard aggregate values specified in the GROUP BY. Then ROLLUP moves from right to left through the list of grouping columns and creates progressively higher-level subtotals. A grand total is created at the end. If <n> is the number of grouping columns, ROLLUP creates <n+1> levels of subtotals.

Restrictions on the ROLLUP operator:

ROLLUP supports all of the aggregate functions available to the GROUP BY clause, but ROLLUP does not currently support COUNT DISTINCT and SUM DISTINCT.
ROLLUP can be used only in the SELECT statement; you cannot use ROLLUP in a SELECT subquery.
A multiple grouping specification that combines ROLLUP, CUBE, and GROUP BY columns in the same GROUP BY clause is not currently supported.
Constant expressions as GROUP BY keys are not supported.

GROUPING is used with the ROLLUP operator to distinguish between stored NULL values and NULL values in query results created by ROLLUP.

ROLLUP syntax:

SELECT … [ GROUPING ( <column-name >) …] …
GROUP BY [ <expression> [, …]
| ROLLUP ( <expression> [, …] ) ]

GROUPING takes a column name as a parameter and returns a Boolean value:

Table 1: Values Returned by GROUPING with the ROLLUP Operator
If the Value of the Result Is	GROUPING Returns
NULL created by a ROLLUP operation	1 (TRUE)
NULL indicating the row is a subtotal	1 (TRUE)
not created by a ROLLUP operation	0 (FALSE)
a stored NULL	0 (FALSE)

CUBE operator

analyzes data by forming the data into groups in more than one dimension. CUBE requires an ordered list of grouping expressions (dimensions) as arguments and enables the SELECT statement to calculate subtotals for all possible combinations of the group of dimensions. The CUBE operator is part of the GROUP BY clause.

Restrictions on the CUBE operator:

CUBE supports all of the aggregate functions available to the GROUP BY clause, but CUBE does not currently support COUNT DISTINCT or SUM DISTINCT.
CUBE does not currently support the inverse distribution analytical functions PERCENTILE_CONT and PERCENTILE_DISC.
CUBE can be used only in the SELECT statement; you cannot use CUBE in a SELECT subquery.
A multiple GROUPING specification that combines ROLLUP, CUBE, and GROUP BY columns in the same GROUP BY clause is not currently supported.
Constant expressions as GROUP BY keys are not supported.

GROUPING is used with the CUBE operator to distinguish between stored NULL values and NULL values in query results created by CUBE.

CUBE syntax:

SELECT … [ GROUPING ( <column-name> ) …] …
GROUP BY [ <expression> [, …]
| CUBE ( <expression> [, …] ) ]

GROUPING takes a column name as a parameter and returns a Boolean value:

Table 2: Values Returned by GROUPING with the CUBE Operator
If the Value of the Result Is	GROUPING Returns
NULL created by a CUBE operation	1 (TRUE)
NULL indicating the row is a subtotal	1 (TRUE)
not created by a CUBE operation	0 (FALSE)
a stored NULL	0 (FALSE)

When generating a query plan, the SAP IQ optimizer estimates the total number of groups generated by the GROUP BY CUBE hash operation. The MAX_CUBE_RESULTS database option sets an upper boundary for the number of estimated rows the optimizer considers for a hash algorithm that can be run. If the actual number of rows exceeds the MAX_CUBE_RESULT option value, the optimizer stops processing the query and returns the error message “Estimate number: nnn exceed the DEFAULT_MAX_CUBE_RESULT of GROUP BY CUBE or ROLLUP”, where <nnn> is the number estimated by the optimizer. See MAX_CUBE_RESULT Option for information on setting the MAX_CUBE_RESULT option.

HAVING <search-condition> based on the group values and not on the individual row values. The HAVING clause can be used only if either the statement has a GROUP BY clause or if the select list consists solely of aggregate functions. Any column names referenced in the HAVING clause must either be in the GROUP BY clause or be used as a parameter to an aggregate function in the HAVING clause.
ORDER BY
orders the results of a query. Each item in the ORDER BY list can be labeled as ASC for ascending order or DESC for descending order. Ascending is assumed if neither is specified. If the expression is an integer n, then the query results are sorted by the nth item in the select list.

In Embedded SQL, the SELECT statement is used for retrieving results from the database and placing the values into host variables with the INTO clause. The SELECT statement must return only one row. For multiple row queries, you must use cursors.

You cannot include a Java class in the SELECT list, but you can, for example, create a function or variable that acts as a wrapper for the Java class and then select it.

FOR JSON clause specifies that the result set is to be returned in JSON format. The JSON format depends on the mode you specify. This clause cannot be used with the FOR UPDATE or FOR READ ONLY clause. Cursors declared with FOR JSON are implicitly READ ONLY.

When you specify RAW mode, each row in the result set is returned as a flattened JSON representation.

AUTO mode returns the query results as nested JSON objects based on query joins.

EXPLICIT mode allows you to control the form of the generated JSON objects. Using EXPLICIT mode offers more flexibility in specifying columns and nested hierarchical objects to produce uniform or heterogeneous arrays.
<row-limitation-option2>
returns a subset of rows that satisfy the WHERE clause. Only one row-limitation clause can be specified at a time. When specifying this clause, an ORDER BY clause is required to order the rows in a meaningful manner. The row limitation clause is valid only in the top query block of a statement.

The LIMIT argument must be an integer or integer variable The OFFSET argument must evaluate to a value greater than or equal to 0. If <offset-expression> is not specified, the default is 0.

The row limitation clause LIMIT <offset-expression>, <limit-expression> is equivalent to LIMIT <limit-expression> OFFSET <offset-expression>.

The LIMIT keyword is disabled by default. Use the RESERVED_KEYWORDS option to enable the LIMIT keyword.
Note You cannot specify TOP and LIMIT in the same query.

Examples

(back to top)

Example 1 list all tables and views in the system catalog:

SELECT tname
FROM SYS.SYSCATALOG
WHERE tname LIKE 'SYS%' ;

Example 2 list all customers and the total value of their orders:

SELECT CompanyName,
  CAST( sum(SalesOrderItems.Quantity *
  Products.UnitPrice) AS INTEGER) VALUE
FROM Customers
  LEFT OUTER JOIN SalesOrders
  LEFT OUTER JOIN SalesOrderItems
  LEFT OUTER JOIN Products
GROUP BY CompanyName
ORDER BY VALUE DESC

Example 3
list the number of employees:
```
SELECT count(*)
FROM Employees;
```

Example 4 an Embedded SQL SELECT statement:

SELECT count(*) INTO :size FROM Employees;

Example 5 list the total sales by year, model, and color:

SELECT year, model, color, sum(sales) 
FROM sales_tab 
GROUP BY ROLLUP (year, model, color);

Example 6
select all items with a certain discount into a temporary table:
```
SELECT * INTO #TableTemp FROM lineitem 
WHERE l_discount < 0.5
```
Example 7
return information about the employee that appears first when employees are sorted by last name:
```
SELECT FIRST *
FROM Employees
ORDER BY Surname;
```

Example 8 return the first five employees when their names are sorted by last name:

SELECT TOP 5 *
FROM Employees
ORDER BY Surname;

SELECT *
FROM Employees
ORDER BY Surname
LIMIT 5;

Example 9
list the fifth and sixth employees sorted in descending order by last name:
```
SELECT *
FROM Employees
ORDER BY Surname DESC
LIMIT 4,2;
```

Usage

(back to top)

You can use a SELECT statement in Interactive SQL to browse data in the database or to export data from the database to an external file.

You can also use a SELECT statement in procedures or in Embedded SQL. The SELECT statement with an INTO clause is used for retrieving results from the database when the SELECT statement returns only one row. (Tables created with SELECT INTO do not inherit IDENTITY/AUTOINCREMENT tables.) For multiple-row queries, you must use cursors. When you select more than one column and do not use <#table>, SELECT INTO creates a permanent base table. SELECT INTO <#table> always creates a temporary table regardless of the number of columns. SELECT INTO table with a single column selects into a host variable.

Note

When writing scripts and stored procedures that SELECT INTO a temporary table, wrap any select list item that is not a base column in a CAST expression. This guarantees that the column data type of the temporary table is the required data type.

Tables with the same name but different owners require aliases. A query without aliases returns incorrect results:

SELECT * FROM user1.t1
WHERE NOT EXISTS
(SELECT *
FROM user2.t1
WHERE user2.t1.col1 = user1.t.col1);

For correct results, use an alias for each table:

SELECT * FROM user1.t1 U1
WHERE NOT EXISTS
(SELECT *
FROM user2.t1 U2
WHERE U2.col1 = U1.col1);

The INTO clause with a <variable-list> is used only in procedures.

In SELECT statements, a stored procedure call can appear anywhere a base table or view is allowed. Note that CIS functional compensation performance considerations apply. For example, a SELECT statement can also return a result set from a procedure.

Standards

(back to top)

SQL—ISO/ANSI SQL compliant.
SAP Database products—Supported by SAP IQ, with some differences in syntax.

Permissions

(back to top)

Requires SELECT privilege on the named tables and views.