SELECT statement

User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements
SELECT statement

Function

To retrieve information from the database.

Syntax

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

Parameters

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

Usage

Anywhere

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.

Permissions

Must have SELECT permission on the named tables and views.

Side effects

None.

Description

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

A SELECT statement can be used in ISQL 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.

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 If neither ALL nor DISTINCT is specified, ALL rows which 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. Thus, the use of DISTINCT should be reserved for cases where it is necessary.

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

select list The select list is a list of expressions separated by commas specifying what will be retrieved from the database. If asterisk (*) is specified, it is expanded to select all columns of all tables in the FROM clause (table-name all columns of the named table). Aggregate functions are allowed in the select list (see "Watcom-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 ISQL at the top of each column of output from the SELECT statement. If the optional alias name is not specified after an expression, ISQL will display the expression.

INTO host-variable-list 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 that 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.

See "Search conditions" for a full description.

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

Embedded SQL Example

     SELECT count(*) INTO :size FROM employee

Examples

List all the tables and views in the system catalog.

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

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

How many employees are there?

     SELECT count(*)
     FROM Employee ;