Understanding Data Retrieval with SELECT

From InterBase

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:

INTO :emp_no, :fname, :lname
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


Lists columns to retrieve.


In a singleton SELECT, lists host variables for storing retrieved columns.


Identifies the tables to search for values.


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.


Groups related rows based on common column values. Used in conjunction with HAVING.


Restricts rows generated by GROUP BY to a subset of those rows.


Combines the results of two or more SELECT statements to produce a single, dynamic table without duplicate rows.


Specifies the query plan that should be used by the query optimizer instead of one it would normally choose.


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


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.


Advance To: