SELECT statement

Description

Retrieves information from the database.

Syntax

SELECT [ ALL | DISTINCT ] [ FIRST | TOP number-of-rows ] select-list
... [ INTO { host-variable-list | variable-list | table-name } ]
... [ FROM table-list ]
... [ WHERE search-condition ]
... [ GROUP BY [ expression [,...]
      | ROLLUP ( expression [,...] )
      | CUBE ( expression [,...] ) ] ] 
... [ HAVING search-condition ]
... [ ORDER BY { expression | integer } [ ASC | DESC ] [, ...] ]

Parameters

Examples

Example 1

• List all the 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 company_name,
	CAST( sum(sales_order_items.quantity *
	product.unit_price) AS INTEGER) VALUE
FROM customer
	LEFT OUTER JOIN sales_order
	LEFT OUTER JOIN sales_order_items
	LEFT OUTER JOIN product
GROUP BY company_name
ORDER BY VALUE DESC

Example 3

• How many employees are there?

SELECT count(*)
FROM Employee ;

Example 4

• The following statement shows an Embedded SQL SELECT statement:

SELECT count(*) INTO :size FROM employee

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

Usage

The SELECT statement is used for retrieving results from the database.

A SELECT statement can be used in DBISQL to browse data in the database or to export data from the database to an external file.

A SELECT statement can also be used 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 only returns 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 like the following 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.

A SELECT statement can also be used to return a result set from a procedure. The various parts of the SELECT statement are described below:

If DISTINCT is used, the statement cannot contain an aggregate function with a DISTINCT parameter.

FIRST and TOP are used primarily with the ORDER BY clause. If these keywords are used without an ORDER BY clause, then the result may vary from run to run of the same query, as the optimizer may 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 may 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, except that the maximum number of rows returned for TOP is 32767. ROW_COUNT does not have an upper limit for the number of rows returned. If both TOP and ROW_COUNT are set, then the value of TOP takes precedence.

If the table name starts with # then 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 the following 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.

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 queries that are valid Adaptive Server Anywhere queries. Sybase IQ extensions are not supported.

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

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. The following exception applies: The select-list and HAVING clause may contain aggregate functions.

You can use GROUP BY to calculate results or display a column or expression that does not appear in the select list. The GROUP BY clause displays all groups, even those excluded from calculations by a WHERE clause.

The following extensions to ANSI standards GROUP BY are supported in order to make Adaptive Server Enterprise queries run easily with Sybase IQ.

• A select list that includes aggregates can include extended columns that are not arguments of aggregate functions and are not included in the GROUP BY clause.

• The GROUP BY ALL clause displays all groups, even those excluded from calculations by a WHERE clause.

• The HAVING clause can include columns or expressions that are not in the select list and not in the group by clause

• An item in the ORDER BY clause could also contain extended columns as long as that item is either in the select list or the GROUP BY clause.

Some exceptions apply to this support:

• If any extended column is from a view or derived table that is base on a query with group by, distinct or aggregation syntax, IQ will reject the query.

• If there are two or more base tables involved in the extended group by query, IQ rejects the query. This means IQ only supports the extended group by clause for single table reference.

• ASE does not support any OLAP function, so if there is any OLAP function in the extended group by clause, IQ rejects the query.

See the Examples in this section.

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, then ROLLUP creates n+1 levels of subtotals.

Restrictions on the ROLLUP operator are:

• The ROLLUP operator 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 only be used 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 [,...] ) ] 

select size, name, color, SUM(quantity),
grouping (size) as S,
grouping (name) as N,
grouping (color) as C
from product
group by rollup (size, name, color)
having (S=1 or N=1 or C=1)
order by size, name, color;

select ship_date, prod_id, SUM(quantity),
case grouping (prod_id) when 1 then 'ALL' else CAST (prod_id as VARCHAR(15) END as PROD_IDS,
case grouping (ship_date) when 1 then 'ALL' else CAST (ship_date as VARCHAR(20)) END as SHIP_DATE
from sales_order_items
group by rollup (ship_date, prod_id)
having SUM(quantity) > 36 ORDER BY ship_date, prod_id;

Restrictions on the CUBE operator are:

• The CUBE operator supports all of the aggregate functions available to the GROUP BY clause, but CUBE does not currently support COUNT DISTINCT and SUM DISTINCT.

• CUBE can only be used 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 [,...] ) ] 

See the examples in the description of the ROLLUP operator for illustrations of the use of the GROUPING function to interpret results.

In Embedded SQL, the SELECT statement is used for retrieving results from the database and placing the values into host variables via 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.

None.

Standards

Permissions

Must have SELECT permission on the named tables and views.

See also