SELECT

Go Up to Statement and Function Reference (Language Reference Guide)

Retrieves data from one or more tables. Available in gpre, DSQL, and isql.

SELECT [TRANSACTION transaction]
[DISTINCT | ALL] 
{* | <val> [, <val> …]}
[INTO :<var> [, :<var> …]]
FROM tableref [, <tableref> …]
[WHERE <search_condition>]
[GROUP BY <col> [COLLATE <collation>] [, <col> [COLLATE <collation>] …] 
[HAVING <search_condition>] 
[UNION [ALL] <select_expr>]
[PLAN <plan_expr>]
[ORDER BY <order_list>]
[ROWS <value> [TO <upper_value>] [BY <step_value>][PERCENT][WITH TIES]]
[FOR UPDATE [OF <col> [, <col> …]]];

val = {
col [array_dim] | :variable

 | constant | expr | function

 | udf ([val [, val …]])
| NULL | USER | RDB$DB_KEY | ?
} [COLLATE collation] [AS alias]
array_dim = [[x:]y [, [x:]y …]]
constant = num | 'string' | charsetname 'string'
function = COUNT (* | [ALL] val | DISTINCT val)
| SUM ([ALL] val | DISTINCT val)
| AVG ([ALL] val | DISTINCT val)
| MAX ([ALL] val | DISTINCT val)
| MIN ([ALL] val | DISTINCT val)
| CAST (val AS datatype)
| UPPER (val)
| GEN_ID (generator, val)
tableref = joined_table | table | view | procedure

 [(val [, val …])] [alias]
joined_table = tableref join_type JOIN tableref

 ON search_condition | (joined_table)
join_type = [INNER] JOIN
| {LEFT | RIGHT | FULL } [OUTER]}
search_condition = val operator {val | (select_one)}
| val [NOT] BETWEEN val AND val
| val [NOT] LIKE val [ESCAPE val]
| val [NOT] IN (val [, val …] | select_list)
| val IS [NOT] NULL
| val {&gt;= | <=} val
| val [NOT] {= | < | >} val
| {ALL | SOME | ANY} (select_list)
| EXISTS (select_expr)
| SINGULAR (select_expr)
| val [NOT] CONTAINING val
| val [NOT] STARTING [WITH] val
| (search_condition)
| NOT search_condition
| search_condition OR search_condition
| search_condition AND search_condition
operator = {= | < | > | <= | >= | !< | !> | <> | !=}
plan_expr =
[JOIN | [SORT] [MERGE]] ({plan_item | plan_expr}
[, {plan_item | plan_expr} …])
plan_item = {table | alias}
{NATURAL | INDEX (index [, index …]) | ORDER index}
order_list =
{col | int} [COLLATE collation]
[ASC[ENDING] | DESC[ENDING]]
[, order_list …]

Argument	Description
<expr>	A valid SQL expression that results in a single value
<select_one>	A SELECT on a single column that returns exactly one value
<select_list>	A SELECT on a single column that returns zero or more rows
<select_expr>	A SELECT on a list of values that returns zero or more rows

Argument	Description
TRANSACTION <transaction>	Name of the transaction under control of which the statement is executed; SQL only
SELECT[DISTINCT|ALL]	Specifies data to retrieve DISTINCT prevents duplicate values from being returned ALL, the default, retrieves every value
{*|<val> [, <val> …]}	The asterisk (*) retrieves all columns for the specified tables <val> [, <val> …] retrieves a list of specified columns, values, and expressions
INTO : [, …]	Singleton select in embedded SQL only; specifies a list of host-language variables into which to retrieve values
FROM <tableref> [, <tableref> …]	List of tables, views, and stored procedures from which to retrieve data; list can include joins and joins can be nested
<table>	Name of an existing table in a database
<view>	Name of an existing view in a database
<procedure>	Name of an existing stored procedure that functions like a SELECT statement
<alias>	Brief, alternate name for a table, view, or column; after declaration in <tableref>, <alias> can stand in for subsequent references to a table or view
<joined_table>	A table reference consisting of a JOIN
<join_type>	Type of join to perform. Default: INNER
WHERE <search_condition>	Specifies a condition that limits rows retrieved to a subset of all available rows A WHERE clause can contain its own SELECT statement, referred to as a subquery
GROUP BY <col> [, <col> …]	Groups related rows based on common column values; used in conjunction with HAVING
COLLATE <collation>	Specifies the collation order for the data retrieved by the query
HAVING <search_condition>	Used with GROUP BY; specifies a condition that limits the grouped rows returned
UNION[ALL]	Combines the results of two or more SELECT statements to produce a single, dynamic table without duplicate rows The ALL option keeps identical rows separate instead of folding them together into one
PLAN <plan_expr>	Specifies the query plan that should be used by the query optimizer instead of one it would normally choose
<plan_item>	Specifies a table and index method for a plan
ORDER BY <order_list>	Specifies columns to order, either by column name or ordinal number in the query, and the sort order (ASC or DESC) for the returned rows
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 or highest percent to return If <step_value> = <n>, returns every <n>th row, or <n> percent rows PERCENT causes all previous ROWS values to be interpreted as percents WITH TIES returns additional duplicate rows when the last value in the ordered sequence is the same as values in subsequent rows of the result set; 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

Description: SELECT retrieves data from tables, views, or stored procedures. Variations of the SELECT statement make it possible to:

• Retrieve a single row, or part of a row, from a table. This operation is referred to as a singleton select.

In embedded applications, all SELECT statements that occur outside the context of a cursor must be singleton selects.

• Retrieve multiple rows, or parts of rows, from a table.

In embedded applications, multiple row retrieval is accomplished by embedding a SELECT within a DECLARE CURSOR statement.

In isql, SELECT can be used directly to retrieve multiple rows.

• Retrieve related rows, or parts of rows, from a join of two or more tables.
• Retrieve all rows, or parts of rows, from union of two or more tables.
• Return portions or sequential portions of a larger result set; useful for Web developers, among others.

All SELECT statements consist of two required clauses (SELECT, FROM), and possibly others (INTO, WHERE, GROUP BY, HAVING, UNION, PLAN, ORDER BY, ROWS).

Because SELECT is such a ubiquitous and complex statement, a meaningful discussion lies outside the scope of this reference. To learn how to use SELECT in isql, see the Operations Guide. For a complete explanation of SELECT and its clauses, see the Embedded SQL Guide.

Notes on SELECT syntax:

• When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:

my_array = varchar(6)[5,5]

Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integer that begins at 10 and ends at 20:

my_array = integer[20:30]

• In SQL and isql, you cannot use val as a parameter placeholder (like “?”).
• In DSQL and isql, val cannot be a variable.
• You cannot specify a COLLATE clause for Blob columns.
• You cannot specify a GROUP BY clause for Blob and array columns.

Important: In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Examples: The following isql statement selects columns from a table:

SELECT JOB_GRADE, JOB_CODE, JOB_COUNTRY, MAX_SALARY FROM PROJECT;

The next isql statement uses the * wildcard to select all columns and rows from a table:

SELECT * FROM COUNTRIES;

The following embedded SQL statement uses an aggregate function to count all rows in a table that satisfy a search condition specified in the WHERE clause:

EXEC SQL
SELECT COUNT (*) INTO :cnt FROM COUNTRY
WHERE POPULATION > 5000000;

The next isql statement establishes a table alias in the SELECT clause and uses it to identify a column in the WHERE clause:

SELECT C.CITY FROM CITIES C
WHERE C.POPULATION < 1000000;

The following isql statement selects two columns and orders the rows retrieved by the second of those columns:

SELECT CITY, STATE FROM CITIES
ORDER BY STATE;

The next isql statement performs a left join:

SELECT CITY, STATE_NAME FROM CITIES C
LEFT JOIN STATES S ON S.STATE = C.STATE
WHERE C.CITY STARTING WITH 'San';

The following isql statement specifies a query optimization plan for ordered retrieval, utilizing an index for ordering:

SELECT * FROM CITIES
PLAN (CITIES ORDER CITIES_1);
ORDER BY CITY

The next isql statement specifies a query optimization plan based on a three-way join with two indexed column equalities:

SELECT * FROM CITIES C, STATES S, MAYORS M
WHERE C.CITY = M.CITY AND C.STATE = M.STATE
PLAN JOIN (STATE NATURAL, CITIES INDEX DUPE_CITY,
MAYORS INDEX MAYORS_1);

The next example queries two of the system tables, RDB$CHARACTER_SETS and RDB$COLLATIONS to display all the available character sets, their ID numbers, number of bytes per character, and collations. Note the use of ordinal column numbers in the ORDER BY clause.

SELECT RDB$CHARACTER_SET_NAME, RDB$CHARACTER_SET_ID,
RDB$BYTES_PER_CHARACTER, RDB$COLLATION_NAME
FROM RDB$CHARACTER_SETS JOIN RDB$COLLATIONS
ON RDB$CHARACTER_SETS.RDB$CHARACTER_SET_ID =
RDB$COLLATIONS.RDB$CHARACTER_SET_IefD
ORDER BY 1, 4;

The following examples reward the best performing sales people and terminate the least performing members of the sales team. The examples show how a Web developer, for example, could split the result set in half for display purposes.

SELECT SALESMAN, SALES_DOLLARS, SALES_REGION
FROM SALESPEOPLE
ORDER BY SALES_DOLLARS DESC
ROWS 1 TO 50;
SELECT SALESMAN, SALES_DOLLARS, SALES_REGION
FROM SALESPEOPLE
ORDER BY SALES_DOLLARS DESC
ROWS 50 TO 100 WITH TIES;

Reward the best 100 performing salesmen with a 15 percent bonus:

UPDATE SALESPEOPLE
SET SALES_BONUS = 0.15 * SALES_DOLLARS
ORDER BY SALES_DOLLARS DESC
ROWS 100 WITH TIES;

Eliminate the worst five percent of the sales force:

DELETE FROM SALESPEOPLE
ORDER BY SALES_DOLLARS
ROWS 5 PERCENT WITH TIES;

See Also

• DECLARE CURSOR
• DELETE
• INSERT
• UPDATE
• Embedded SQL Guide