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

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.

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.

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

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

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

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:

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

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 or SUM DISTINCT.

• CUBE does not currently support the inverse distribution analytical functions, PERCENTILE_CONT and PERCENTILE_DISC.

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

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.

None.

Standards

Permissions

Must have SELECT permission on the named tables and views.

See also