SELECT

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

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

Syntax

SELECT [TRANSACTION transact] [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 
  | funct | udf ([val [, val …]]) 
  | NULL | USER | RDB$DB_KEY | ? }
  [COLLATE collation] [AS alias]

array_dim = [[x:]y [, [x:]y …]]

constant = num | 'string' | charsetname 'string'

funct = 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 data_type)
  | UPPER (val)
  | GEN_ID (generator, val)

tableref = <joined_table> | <table_primary>

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 {>= | <=} 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 = {= | < | > | <= | >= | !< | !> | <> | !=}

table_primary = [{table | view | procedure} [[AS] alias]] | <derived_table>
  
derived_table = query_expression [AS] alias

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.
`TRANSACTION transact`	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 :<var> [, <var> …]`	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, stored procedures or derived tables from which to retrieve data; list can include joins and joins can be nested.
`<joined_table>`	A table reference consisting of a `JOIN`.
`<join_type>`	Type of join to perform. Default: `INNER`.
`<table_primary>`	Name of an existing table, view, stored procedure or a derived table.
`alias`	Alternate name for a table, view, or column.
`<derived_table>`	A result set of a `SELECT` query that you can use in the `FROM` clause. See Derived Tables (SELECT FROM SELECT) for more information and examples.
`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`. To learn about the enhancements to `GROUP BY` introduced in InterBase 2017 Update 1 Refer to: Enhancements to GROUP BY and ORDER BY.
`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. To learn about the enhancements to `ORDER BY` introduced in InterBase 2017 Update 1 Refer to: Enhancements to GROUP BY and ORDER BY.
`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.

Note:
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).

For more information on how to use SELECT in isql, see the Operations Guide. For a complete explanation of SELECT and its clauses, see the Embedded SQL Guide.

Derived Tables (SELECT FROM SELECT)

A derived table is the result set of a SELECT query that you can use in the FROM clause. You may find it useful to think of a derived table as a view with statement-level scope. This allows you the expressive flexibility to use a view-like structure without defining a database schema view, or allows the user to obtain the same benefit in an ad-hoc query without requiring a database administer to create a view definition.

You can use derived tables in triggers and stored procedures as well as user applications, but you must have proper access privileges on the underlying base tables and views accessed by a derived table.

Dynamic SQL and isql support derived table syntax, Embedded SQL does not support derived table syntax. For further info on Derived Tables refer to SQL Derived Table Support

Examples With Derived Tables

The following simple example shows how you can use derived tables:

SELECT elj.job_code,
       elj.job_title
FROM   ( SELECT job_code,
                job_title
         FROM   job
         WHERE  max_salary < 50000 ) AS elj;

The statement queries the EMPLOYEE table for entry-level jobs.

The following is a more complex statement using derived tables:

SELECT emp.emp_no,
       emp.full_name,
       emp.job_code,
       job.job_grade,
       job.job_title
FROM   ( SELECT emp_no,
                full_name,
                job_code,
                job_grade,
                job_country
         FROM   employee ) AS emp,
       ( SELECT job_code,
                job_grade,
                job_country,
                job_title
         FROM   job ) AS job
WHERE  ( emp.job_code = job.job_code ) AND
       ( emp.job_grade = job.job_grade ) AND
       ( emp.job_country = job.job_country ) AND
       ( emp.job_country = 'USA' );

The following example shows a derived table with a subquery:

SELECT eid,
       ename
FROM   ( SELECT e.emp_no,
                e.full_name
         FROM   employee e
         WHERE  e.job_country =
                ( SELECT e1.job_country
                  FROM   employee e1
                  WHERE  emp_no = 144 ) ) AS emp (eid, ename);

Additional Notes on Derived Tables

Derived tables can be nested.
Derived tables can be unions and can be used in unions. They can contain aggregate functions, subselects and joins, and can themselves be used in aggregate functions, subselects and joins. They can also be or contain queries on selectable stored procedures.

Additional Notes on SELECT

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.

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;

Enhancements to GROUP BY and ORDER BY

InterBase 2017 Update 1 introduces enhancements to GROUP BY and ORDER BY syntax that are not necessarily SQL standard, but nonetheless allow expressive behavior of grouping and ordering query result sets. For more information refer to: Enhancements to GROUP BY and ORDER BY.

Advance To:

Enhancements to GROUP BY and ORDER BY