SELECT Statement

Retrieves information from the database.

Syntax

SELECT [ ALL | DISTINCT ] [ FIRST | TOP number-of-rows ] 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:

   { column-name
   | expression [ [ AS ] alias-name ]
   | * }

row-limitation-option:

   LIMIT { [ offset-expression, ] limit-expression 
   | limit-expression OFFSET offset-expression }

limit-expression:

simple-expression

offset-expression:

simple-expression

simple-expression:

integer
   | variable
   | ( simple-expression )
   | ( simple-expression { + | - | * } simple-expression )

Parameters

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.
FIRST or TOP number-of-rows – 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.
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 Sybase IQ, scalar subqueries (nested selects) are allowed in the select list of the top level SELECT, as in SQL Anywhere and Adaptive Server Enterprise. 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 SQL Anywhere queries. SAP Sybase 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 SQL Anywhere instead of SAP Sybase 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 Sybase 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 Sybase 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:

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:

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 Sybase 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 clause – 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 LIMIT and OFFSET arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The LIMIT argument must evaluate to a value greater than or equal to 0. 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.

Examples

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;
```

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

CREATE OR REPLACE VARIABLE atop INT = 10;

SELECT TOP (atop -5) *
FROM Employees
ORDER BY Surname;

SELECT *
FROM Employees
ORDER BY Surname
LIMIT (atop-5);

Example 11 – list the fifth and sixth employees sorted in descending order by last name:

CREATE OR REPLACE VARIABLE atop INT = 10;

SELECT *
FROM Employees
ORDER BY Surname DESC
LIMIT (atop - 8) OFFSET (atop -2 -3 -1);

Usage

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

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

Permissions

Requires SELECT privilege on the named tables and views.