SELECT Statement

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 ]

Parameters

• 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 )

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.

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.

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.

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:

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

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

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

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

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

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.

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

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

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

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

Permissions

Requires SELECT privilege on the named tables and views.