Collection Contents Index SAVEPOINT statement SET statement pdf/chap9.pdf

Reference Manual
   CHAPTER 9. SQL Statements     

SELECT statement


Function 

To retrieve information from the database.

Syntax 

SELECT [ ALL | DISTINCT ] [ FIRST | TOP n ] select-list
...[ INTO { host-variable-list | variable-list } ]
...[ FROM table-list ]
...[ WHERE search-condition ]
...[ GROUP BY column-name | alias | function ... ]
...[ HAVING search-condition ]
...[ ORDER BY { expression | integer } [ ASC | DESC ], ... ]

Parameters 

select-list:
table-name
| expression [ [ AS ] alias-name ]
| *

Permissions 

Must have SELECT permission on the named tables and views.

Side effects 

None.

See also 

CREATE VIEW statement

DECLARE CURSOR statement

Expressions

FETCH statement

FROM clause

OPEN statement

Search conditions

UNION operation

Description 

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

A SELECT statement can be used in Interactive SQL 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 and triggers 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. For multiple row queries, you must use cursors.

The INTO clause with host-variable-list is used in Embedded SQL only.

The INTO clause with variable-list is used in procedures and triggers 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:

ALL or DISTINCT     All (the default) returns all rows that satisfy the clauses of the SELECT statement. If DISTINCT is specified, duplicate output rows are eliminated. This is called the projection of the result of the statement. Because many, statements take significantly longer to execute when DISTINCT is specified, You should reserve 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     These keywords are principally for use with ORDER BY queries. You can explicitly retrieve only the first row of a query or the first n rows of a query.

The FIRST and TOP keywords cannot be used in a derived table query. You should not use the keywords in view definitions.

select list     The select list is a list of expressions, separated by commas, specifying what will be retrieved from the database. Asterisk (*) means to select all columns of all tables in the FROM clause (if you specify a table-name, all comlumns of the specifed table are selected).

Aggregate functions are allowed in the select list (see SQL Functions). Subqueries are also allowed in the select list (see Expressions). Each subquery must be within parentheses.

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 will display the expression itself.

INTO host-variable-list     This clause is used in Embedded SQL only. It specifies where the results of the SELECT statement will go. 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 and triggers only. It specifies where the results of the SELECT statement will go. There must be one variable for each item in the select list. Select list items are put into the variables in order.

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     This clause specifies which rows will be 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.

For Info     For more information, see Search conditions.

GROUP BY { column-name | alias | function }, ...     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.

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 exception is that the select list and HAVING clause may contain aggregate functions.

HAVING search-condition     This clause selects rows based on the group values and not on the individual row values. The HAVING clause can only be used if either the statement has a GROUP BY clause or 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 expression, ...     This clause sorts the results of a query. Each item in the ORDER BY list can be labeled as ASC for ascending order (the default) or DESC for descending order. If the expression is an integer n, then the query results will be sorted by the n'th 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 via the INTO clause. The SELECT statement must return only one row. For multiple row queries, you must use cursors.

Standards and compatibility 

Examples 


Collection Contents Index SAVEPOINT statement SET statement pdf/chap9.pdf