Understanding Data Retrieval with SELECT

Go Up to Working with Data

The SELECT statement handles all queries in SQL. SELECT can retrieve one or more rows from a table, and can return entire rows, or a subset of columns from each row, often referred to as a projection. Optional SELECT syntax can be used to specify search criteria that restrict the number of rows returned, to select rows with unknown values, to select rows through a view, and to combine rows from two or more tables.

At a minimum, every SELECT statement must:

• List which columns to retrieve from a table. The column list immediately follows the SELECT keyword.
• Name the table to search in a FROM clause.

Singleton selects must also include both an INTO clause to specify the host variables into which retrieved values should be stored, and a WHERE clause to specify the search conditions that cause only a single row to be returned.

The following SELECT retrieves three columns from a table and stores the values in three host-language variables:

EXEC SQL
SELECT EMP_NO, FIRSTNAME, LASTNAME
INTO :emp_no, :fname, :lname
FROM EMPLOYEE WHERE EMP_NO = 1888;

Tip:
Host variables must be declared in a program before they can be used in SQL statements. For more information about declaring host variables, see Application Requirements.

The following table lists all SELECT statement clauses, in the order that they are used, and prescribes their use in singleton and multi-row selects:

SELECT statement clauses

Clause	Purpose
SELECT	Lists columns to retrieve.
INTO	In a singleton SELECT, lists host variables for storing retrieved columns.
FROM	Identifies the tables to search for values.
WHERE	Specifies the search conditions used to restrict retrieved rows to a subset of all available rows. A WHERE clause can contain its own SELECT statement, referred to as a subquery.
GROUP BY	Groups related rows based on common column values. Used in conjunction with HAVING.
HAVING	Restricts rows generated by GROUP BY to a subset of those rows.
UNION	Combines the results of two or more SELECT statements to produce a single, dynamic table without duplicate rows.
PLAN	Specifies the query plan that should be used by the query optimizer instead of one it would normally choose.
ORDER BY	Specifies the sort order of rows returned by a SELECT, either ascending (ASC), the default, or descending (DESC).
ROWS <value> [TO <upper_value>] [BY <step_value>] [PERCENT][WITH TIES]	<value> is the total number of rows to return if used by itself <value> is the starting row number to return if used with TO <value> is the percent if used with PERCENT <upper_value> is the last row to return If <step_value> = <n>, returns every <n>th row <value> PERCENT returns <n>% of the rows when <value>=<n> WITH TIES returns duplicate rows; must be used in conjunction with ORDER BY
FOR UPDATE	Specifies columns listed after the SELECT clause of a DECLARE CURSOR statement that can be updated using a WHERE CURRENT OF clause; multi-row SELECTs only.

Using each of these clauses with SELECT is described in the following sections, after which using SELECT directly to return a single row, and using SELECT within a DECLARE CURSOR statement to return multiple rows are described in detail. For a complete overview of SELECT syntax, see the Language Reference Guide.

Topics

• Listing Columns to Retrieve with SELECT
• Specifying Transaction Names When Using Select
• Specifying Host Variables with INTO
• Listing Tables to Search with FROM
• Restricting Row Retrieval with WHERE
• Sorting Rows with ORDER BY
• Grouping Rows with GROUP BY
• Restricting Grouped Rows with HAVING
• Limiting Result Sets with ROWS
• Appending Tables with UNION
• Specifying a Query Plan with PLAN

Advance To:

• Listing Columns to Retrieve with SELECT