SELECT Statement

Retrieves information from the database.

Syntax

SELECTALL | DISTINCT ] [ FIRST | TOP number-of-rows ] select-list
… [ INTOhost-variable-list | variable-list | table-name } ]
… [ INTO LOCAL TEMPORARY TABLEtable-name } ]
… [ FROM table-list ]
… [ WHERE search-condition ]
… [ GROUP BYexpression [, …]
      | ROLLUPexpression [, …] )
      | CUBEexpression [, …] ) ] ] 
… [ HAVING search-condition ]
… [ ORDER BYexpression | integer } [ ASC | DESC ] [, …] ]
… [ row-limitation-option ]

Parameters

Examples

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. For syntax and an example, see SQL Anywhere Server – SQL Reference > SQL statements > SQL statements > FROM clause.
Note: This reference points to SQL Anywhere documentation.

See System Administration Guide: Volume 2 > Using Procedures and Batches > Introduction to Procedures > Returning Procedure Results in Result Sets for a restriction that affects selecting from temporary tables within stored procedures.

ALL or DISTINCT—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—The select-list is a list of expressions, separated by commas, specifying 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. See Reference: Building Blocks, Tables, and Procedures > SQL Functions.

Note: In 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.

For more details on the use of subqueries, see Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Expressions > Subqueries in Expressions and Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Search Conditions > Subqueries in Search Conditions.

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—Used in Embedded SQL only. It 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.

INTO variable-list—Used in procedures only. It 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.

INTO table-name—Used to create a table and fill 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. RESOURCE authority is required 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. For more information, see System Administration Guide: Volume 2 > Using Procedures and Batches > Control Statements > Atomic Compound Statements and System Administration Guide: Volume 2 > Using Procedures and Batches > Transactions and Savepoints in Procedures.

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. 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—Rows are retrieved from the tables 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 Sybase IQ and might behave differently, especially with respect to syntactic and semantic restrictions and the effects of option settings. See the SQL Anywhere documentation for rules that might apply to processing.

If you have a query that does not require a FROM clause, you can force the query to be processed by 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. See Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Search Conditions.

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. See Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Search Conditions > Subqueries in Search Conditions > Disjunction of Subquery Predicates.

GROUP BY—You can group by columns or 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—The ROLLUP operator in the GROUP BY clause lets you analyze subtotals using different levels of detail. It creates subtotals 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 …GROUPINGcolumn-name ) …] …
GROUP BYexpression [, …]
| ROLLUPexpression [, …] ) ] 

See Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Expressions for the format of an operator 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)

For ROLLUP examples, see System Administration Guide: Volume 2 > Using OLAP.

CUBE operator—The CUBE operator in the GROUP BY clause 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.

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 … [ GROUPINGcolumn-name ) …] …
GROUP BYexpression [, …]
| CUBEexpression [, …] ) ] 

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 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.

For CUBE examples, see System Administration Guide: Volume 2 > Using OLAP.

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.

row-limitation clause—The row limitation clause allows you to return only a subset of the 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.

Standards

  • SQL—ISO/ANSI SQL compliant.

  • Sybase—Supported by Adaptive Server Enterprise, with some differences in syntax.

Permissions

Must have SELECT permission on the named tables and views.

Related reference
CREATE VIEW Statement
DECLARE CURSOR Statement [ESQL] [SP]
FETCH Statement [ESQL] [SP]
FROM Clause
MAX_CUBE_RESULT Option
OPEN Statement [ESQL] [SP]
UNION Operation
RESERVED_KEYWORDS Option
ROW_COUNT Option
SUBQUERY_CACHING_PREFERENCE Option