SELECT statement

Description

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 ] [, …] ]

Parameters

select-list:

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

Examples

Example 1

Lists all the tables and views in the system catalog:

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

Example 2

Lists 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

Lists the number of employees:

SELECT count(*)
FROM Employees;

Example 4

Shows an Embedded SQL SELECT statement:

SELECT count(*) INTO :size FROM Employees;

Example 5

Lists 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

Selects all items with a certain discount into a temporary table:

SELECT * INTO #TableTemp FROM lineitem 
WHERE l_discount < 0.5

Usage

You can use a SELECT statement in dbisql 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.

NoteSybase recommends that, when writing scripts and stored procedures that SELECT INTO a temporary table, you wrap any select list item that is not a base column in a CAST expression. This guarantees that the temporary table's column data type is the data type desired.

Tables with the same name but different owners require aliases. A query like this 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, as follows:

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 in procedures only.

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 ”FROM clause” in the SQL Anywhere Server – SQL Reference. See “Creating and selecting from temporary tables” in Chapter 1, “Using Procedures and Batches” in the System Administration Guide: Volume 2 for a restriction that affects selecting from temporary tables within stored procedures.

The various parts of the SELECT statement are described below:

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 Chapter 4, “SQL Functions” in Reference: Building Blocks, Tables, and Procedures.

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

In Sybase IQ, 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 “Subqueries in expressions” and “Subqueries in search conditions” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures.

alias-names can be used throughout the query to represent the aliased expression. Alias names are also displayed by dbisql at the top of each column of output from the SELECT statement. If the optional alias-name is not specified after an expression, dbisql 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 This clause is 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 This clause is 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 This clause is used to create a table and fill it 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. 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 “Atomic compound statements” and “Transactions and savepoints in procedures” in Chapter 1, “Using Procedures and Batches” of the System Administration Guide: Volume 2.

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 This clause 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

NoteIf 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 “Search conditions” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures for a full description.

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 “Disjunction of subquery predicates” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures.

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. For the sake 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 are:

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 “Expressions” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures for the format of an operator expression.

GROUPING takes a column name as a parameter and returns a Boolean value as listed in Table 1-11.

Table 1-11: 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 Chapter 2, “Using OLAP,” in the System Administration Guide: Volume 2.

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 are:

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 as listed in Table 1-12.

Table 1-12: 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 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 IQ optimizer. See “MAX_CUBE_RESULT option” in Chapter 2, “Database Options” for information on setting the MAX_CUBE_RESULT option.

For CUBE examples, see Chapter 2, “Using OLAP,” in the System Administration Guide: Volume 2.

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.


Side effects

None

Standards

Permissions

Must have SELECT permission on the named tables and views.

See also

CREATE VIEW statement

DECLARE CURSOR statement [ESQL] [SP]

FETCH statement [ESQL] [SP]

FROM clause

OPEN statement [ESQL] [SP]

UNION operation

“SUBQUERY_CACHING_PREFERENCE option”

“Search conditions,” “Disjunction of subquery predicates,” and “Expressions” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures

Chapter 2, “Using OLAP,” in System Administration Guide: Volume 2

”Accessing fields and methods of the Java object”

in SQL Anywhere Server – Programming