Specifies a result table. A result table is an intermediate table
of values derived from columns and rows of one or more tables or
views that meet conditions specified by a select expression.
The tables or views that the columns and rows come from are
identified in the FROM clause of the statement.
The basic element of a SELECT statement is called a select
expression. The Select_Expressions HELP topic describes select
expressions in detail.
To retrieve rows of a result table in host language programs, you
must use the DECLARE CURSOR statement or a special form of SELECT
statement called a singleton select. See the SELECT Singleton_
Select statement for more information about a singleton select.
SQL evaluates the clauses of a SELECT statement in the following
order:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. Select list
6. ORDER BY
7. OFFSET
8. LIMIT TO (or FETCH FIRST)
9. OPTIMIZE
After each of these clauses, SQL produces an intermediate result
table that is used in evaluating the next clause.
1 – Environment
You can use the general form of the SELECT statement only in
interactive and dynamic SQL.
2 – Format
select-statement =
select-expr -+->--------------------++--------------------+->
+-> for-update-clause -++-> optimize-clause -+
select-expr =
-+-+-> select-clause ------------+-+------+
| +-> ( select-expr ) -----------+ | |
| +-> TABLE table-ref ----------+ | |
+------ select-merge-clause <-------+ |
+------------------- <-------------------+
+-+--------------------+--+------------------+--+--------------------+->
+-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+
select-merge-clause =
-+-> EXCEPT -+--------------+---+----+-------------------+-->
| +-> DISTINCT --+ | +-> CORRESPONDING --+
+-> INTERSECT -+-------------+-+ +-> NATURAL --------+
| +-> DISTINCT -+ |
+-> MINUS ---------------------+
+-> UNION --+-------------+----+
+-> ALL ------+
+-> DISTINCT -+
select-clause =
SELECT -+-------------+-> select-list -------------------------------+
+-> ALL ------+ |
+-> DISTINCT -+ |
+------------------<-------------------------------------------------+
+-> FROM -+--> table-ref --+-----------------------------------------+
+------ , <------+ |
+--------------------------------------------------------------------+
++--------->-----------------------+-+------------->----------------++
+> WHERE predicate ---------------+ +> GROUP BY -+> <column-name> -+|
+> value-expr ----+|
+------- ,<-------+|
+--------------------------------------<-----------------------------+
++---------->----------+---------------------------------------------->
+-> HAVING predicate -+
select-list =
-+-+----> * ------------------------------------------------------+-+->
| +-+--> value-expr --+--------------------------------------+-+-+ |
| | +-> AS <name> -+----------------------++ | |
| | +-> edit-using-clause -+ | |
| | | |
| ++-> <table-name> ------+-> . * ---------------------------+ |
| +-> <view-name> -------+ |
| +-> <correlation-name> + |
+---------------- , <----------------------------------------------+
table-ref =
--+--> <table-name> --+--+-----------------------------+-->
+--> <view-name> --+ +-> correlation-name-clause --+
+--> derived-table -+ |
+--> joined-table -----------------------------------+
derived-table =
----> ( -+-> select-expr --+-> ) ---->
+-> joined-table -+
joined-table =
--+-------> qualified-join ------+---->
+-------> cross-join ----------+
+-> ( --> joined-table --> ) --+
qualified-join =
-+-> table-ref -+-------------+--> JOIN --> table-ref -+
| +> join-type -+ |
| +---------------------------------------------------+
| ++-> ON predicate -------------------------------+-----------+>
| +-> USING ----> ( -+> <column-name> +-> ) ------+ |
| +------ , <------+ |
+-> table-ref -> NATURAL -+-------------+-> JOIN -> table-ref -+
+> join-type -+
cross-join =
---> table-ref --> CROSS JOIN --> table-ref --->
join-type =
--+-> INNER ------------------+-->
+-> LEFT ---+--> OUTER -----+
+-> RIGHT --+
+-> FULL ---+
correlation-name-clause =
-> AS <correlation-name> +------------------------------------+->
+-> ( -+-> <name-of-column> -+-> ) --+
+-------- , <---------+
order-by-clause =
--> ORDER BY -++-> value-expr -++---------+-+-->
|+-> <integer> ----++-> ASC --+ |
| +-> DESC -+ |
+------------ , <---------------+
offset-clause =
---> OFFSET skip-expression -+-> ROW --+-->
+-> ROWS --+
limit-to-clause =
-+> LIMIT TO -+-> limit-expression -+---------------------------+++---------++>
| | +-> OFFSET skip-expression -+|+-> ROW -+|
| | +-> SKIP skip-expression --+|+-> ROWS -+|
| +-> skip-expression , limit-expression -----------+ |
| |
+> FETCH -+> FIRST -+--+---------------------+--+----------+-> ONLY --------+
+> NEXT --+ +-> limit-expression -+ +-> ROW --+
+-> ROWS -+
for-update-clause =
--> FOR UPDATE -+-------------------------+->
+-+-> OF <column-name> -+-+
+-------- , <---------+
edit-using-clause =
--> EDIT USING -+-> edit-string ---+->
+-> <domain-name> -+
optimize-clause =
--+---------------------------------------------------------------+--->
+-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
| | +-> TOTAL TIME --------+ | |
| | +-> SEQUENTIAL ACCESS -+ | |
| +-> USING <outline-name> ------------------+ |
| +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ |
| | +-> SAMPLED --+ | |
| | +-> AGGRESSIVE + | |
| +-> AS <query-name> -----------------------+ |
+---------------- <----------------------------+
3 – Arguments
3.1 – EDIT_USING
Syntax options:
EDIT USING edit-string|EDIT USING domain-name
Associates an edit string with a value expression. This clause
overrides any EDIT STRING defined for the columns or variables in
the query. This clause is only permitted for interactive SQL.
3.2 – FOR_UPDATE_OF
Specifies the columns in a cursor that you or your program might
later modify with an UPDATE statement. The column names in the
FOR UPDATE clause must belong to a table or view named in the
FROM clause.
You do not have to specify the FOR UPDATE clause of the SELECT
statement to later modify rows using the UPDATE statement:
o If you do specify a FOR UPDATE clause with column names and
later specify columns in the UPDATE statement that are not
in the FOR UPDATE clause, SQL issues a warning message and
proceeds with the update modifications.
o If you do specify a FOR UPDATE clause but do not specify
any column names, you can update any column using the UPDATE
statement. SQL does not issue any messages.
o If you do not specify a FOR UPDATE clause, you can update
any column using the UPDATE statement. SQL does not issue any
messages.
The FOR UPDATE OF clause in a SELECT statement provides UPDATE
ONLY CURSOR semantics by locking all the rows selected.
3.3 – OPTIMIZE AS query name
Assigns a name to the query. You can define the RDMS$DEBUG_FLAGS
logical name or use SET FLAGS with the option 'STRATEGY' to see
the access methods used to produce the results of the query. The
following example shows how to use the OPTIMIZE AS clause:
SQL> DELETE FROM EMPLOYEES E
cont> WHERE EXISTS ( SELECT *
cont> FROM SALARY_HISTORY S
cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID
cont> AND S.SALARY_AMOUNT > 75000)
cont> OPTIMIZE AS DEL_EMPLOYEE;
Leaf#01 FFirst RDB$RELATIONS Card=19
.
.
.
~Query Name : DEL_EMPLOYEE
.
.
.
7 rows deleted
3.4 – OPTIMIZE_FOR
Specifies the preferred optimizer strategy for statements that
specify a select expression. The following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If the optimization level is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
The following examples illustrate the DECLARE CURSOR syntax
for setting a preferred optimization mode:
SQL> DECLARE TEMP1 TABLE CURSOR
cont> FOR
cont> SELECT *
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID > '00400'
cont> OPTIMIZE FOR FAST FIRST;
SQL> --
SQL> DECLARE TEMP2 TABLE CURSOR
cont> FOR
cont> SELECT LAST_NAME, FIRST_NAME
cont> FROM EMPLOYEES
cont> ORDER BY LAST_NAME
cont> OPTIMIZE FOR TOTAL TIME;
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
When the storage map of a table has the attribute PARTITIONING
IS NOT UPDATABLE, the mapping of data to a storage area is
strictly enforced. This is known as strict partitioning.
When queries on such tables use sequential access, the
optimizer can eliminate partitions which do not match the
WHERE restriction rather than scan every partition.
The following example shows a query that deletes selected rows
from a specific partition. This table also includes several
indexes, which may be chosen by the optimizer. Therefore, the
OPTIMIZE clause forces sequential access.
SQL> delete from PARTS_LOG
cont> where parts_id between 10000 and 20000
cont> and expire_date < :purge_date
cont> optimize for sequential access;
Note that all access performed by such queries will be
sequential. Care should be taken that the I/O being used is
acceptable by comparing similar queries using index access.
3.5 – OPTIMIZE USING outline name
Explicitly names the query outline to be used with the select
expression even if the outline ID for the select expression and
for the outline are different.
The following example is the query used to create an outline
named WOMENS_DEGREES:
SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE, D.DEGREE_FIELD, D.YEAR_GIVEN
cont> FROM EMPLOYEES E, DEGREES D WHERE E.SEX = 'F'
cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID
cont> ORDER BY LAST_NAME
By using the OPTIMIZE USING clause and specifying the WOMENS_
DEGREES outline, you can ensure that Oracle Rdb attempts to use
the WOMENS_DEGREES outline to execute a query even if the query
is slightly different as shown in the following example:
SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE, D.DEGREE_FIELD, D.YEAR_GIVEN
cont> FROM EMPLOYEES E, DEGREES D WHERE E.SEX = 'F'
cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID
cont> ORDER BY LAST_NAME
cont> LIMIT TO 10 ROWS
cont> OPTIMIZE USING WOMENS_DEGREES;
~S: Outline WOMENS_DEGREES used <-- the query uses the WOMENS_DEGREES outline
.
.
.
E.LAST_NAME E.EMPLOYEE_ID D.DEGREE D.DEGREE_FIELD D.YEAR_GIVEN
Boyd 00244 MA Elect. Engrg. 1982
Boyd 00244 PhD Applied Math 1979
Brown 00287 BA Arts 1982
Brown 00287 MA Applied Math 1979
Clarke 00188 BA Arts 1983
Clarke 00188 MA Applied Math 1976
Clarke 00196 BA Arts 1978
Clinton 00235 MA Applied Math 1975
Clinton 00201 BA Arts 1973
Clinton 00201 MA Applied Math 1978
10 rows selected
See the CREATE OUTLINE statement for more information on creating
an outline.
3.6 – OPTIMIZE_WITH
Selects one of three optimization controls: DEFAULT (as used by
previous versions of Oracle Rdb), AGGRESSIVE (assumes smaller
numbers of rows will be selected), and SAMPLED (which uses
literals in the query to perform preliminary estimation on
indices).
3.7 – select-expr
See the Select_Expressions HELP topic for a detailed description
of select expressions.
4 – Examples
Example 1: Using the SELECT statement
The following SELECT statement returns all rows from the
EMPLOYEES table in no specific order:
SQL> SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL FROM EMPLOYEES;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
Toliver Alvin A
Smith Terry D
Dietrich Rick NULL
Kilpatrick Janet NULL
.
.
.
100 rows selected
Example 2: Adding an ORDER BY clause to sort rows selected
An ORDER BY clause added to the same SELECT statement causes SQL
to sort the rows according to the LAST_NAME column.
SQL> SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL FROM
cont> EMPLOYEES ORDER BY LAST_NAME;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
Ames Louie A
Andriola Leslie Q
Babbin Joseph Y
Bartlett Dean G
Bartlett Wes NULL
.
.
.
100 rows selected
Example 3: Adding a LIMIT TO clause to return a certain number of
rows
The same SELECT statement with both an ORDER BY clause and a
LIMIT TO clause causes SQL to:
1. Sort all the rows of the EMPLOYEES table according to the
LAST_NAME column
2. Return the first five rows in the ordered set
SQL> SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL FROM
cont> EMPLOYEES ORDER BY LAST_NAME LIMIT TO 5 ROWS;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
Ames Louie A
Andriola Leslie Q
Babbin Joseph Y
Bartlett Dean G
Bartlett Wes NULL
5 rows selected
Example 4: Using the optimize clause to specify an outline and a
query name
The following select query uses a previously defined outline
called WOMENS_DEGREES and also names the query. The RDMS$DEBUG_
FLAGS logical has been set to "Ss":
SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE,
cont> D.DEGREE_FIELD, D.YEAR_GIVEN
cont> FROM EMPLOYEES E, DEGREES D
cont> WHERE E.SEX = 'F'
cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID
cont> ORDER BY LAST_NAME
cont> OPTIMIZE USING WOMENS_DEGREES
cont> AS WOMENS_DEGREES;
~Query Name : WOMENS_DEGREES
~S: Outline WOMENS_DEGREES used
Sort
Cross block of 2 entries
Cross block entry 1
Conjunct Get Retrieval by index of relation EMPLOYEES
Index name EMP_EMPLOYEE_ID [0:0]
Cross block entry 2
Leaf#01 BgrOnly DEGREES Card=165
BgrNdx1 DEG_EMP_ID [1:1] Fan=17
-- Rdb Generated Outline : 16-JUN-1994 11:01
create outline WOMENS_DEGREES
id 'D3A5BC351F507FED820EB704FC3F61E8'
mode 0
as (
query (
subquery (
EMPLOYEES 0 access path index EMP_EMPLOYEE_ID
join by cross to
DEGREES 1 access path index DEG_EMP_ID
)
)
)
compliance optional ;
E.LAST_NAME E.EMPLOYEE_ID D.DEGREE D.DEGREE_FIELD D.YEAR_GIVEN
Boyd 00244 MA Elect. Engrg. 1982
Boyd 00244 PhD Applied Math 1979
Brown 00287 BA Arts 1982
Brown 00287 MA Applied Math 1979
Clarke 00188 BA Arts 1983
Clarke 00188 MA Applied Math 1976
Clarke 00196 BA Arts 1978
.
.
.
61 rows selected
Example 5: Associating an Edit String with a Value Expression
SQL> CREATE DOMAIN MONEY INTEGER(2)
cont> EDIT STRING '$$$,$$$,$$9.99';
SQL> --Calculate the average salary for all current jobs.
SQL> SELECT EMPLOYEE_ID,
cont> AVG(SALARY_AMOUNT) AS AVERAGE EDIT USING MONEY,
cont> MAX(SALARY_AMOUNT) AS MAXIMUM EDIT USING MONEY,
cont> MAX(SALARY_START) AS START_DATE EDIT USING 'YYYBDDBMMMBWWW'
cont> FROM SALARY_HISTORY
cont> WHERE SALARY_END IS NULL
cont> GROUP BY EMPLOYEE_ID;
EMPLOYEE_ID AVERAGE MAXIMUM START_DATE
00164 $51,712.00 $51,712.00 983 14 Jan Fri
00165 $11,676.00 $11,676.00 982 1 Jul Thu
00166 $18,497.00 $18,497.00 982 7 Aug Sat
00167 $17,510.00 $17,510.00 982 21 Aug Sat
.
.
.
00435 $84,147.00 $84,147.00 982 12 Mar Fri
00471 $52,000.00 $52,000.00 982 15 Aug Sun
100 rows selected
Example 6: Using the ORDER BY Clause with a Value Expression
SQL> SELECT * FROM EMPLOYEES
cont> ORDER BY EXTRACT (YEAR FROM BIRTHDAY),
cont> TRIM(FIRST_NAME) || TRIM(LAST_NAME);
00190 O'Sullivan Rick G.
78 Mason Rd. NULL Fremont
NH 03044 M 12-Jan-1923 1 None
00231 Clairmont Rick NULL
92 Madiso7 Drive NULL Chocorua
NH 03817 M 23-Dec-1924 2 None
00183 Nash Walter V.
197 Lantern Lane NULL Fremont
NH 03044 M 19-Jan-1925 1 None
00177 Kinmonth Louis NULL
76 Maple St. NULL Etna
NH 03750 M 7-Apr-1926 1 None
00240 Johnson Bill R.
20 South St NULL Milford
NH 03055 M 13-Apr-1927 2 None
.
.
.
Example 7: Using the GROUP BY Clause with a Value Expression
SQL> SELECT COUNT (*), EXTRACT (YEAR FROM BIRTHDAY)
cont> FROM EMPLOYEES
cont> GROUP BY EXTRACT (YEAR FROM BIRTHDAY);
1 1923
1 1924
1 1925
1 1926
4 1927
2 1928
1 1930
2 1931
.
.
.
Example 8: Performing an Outer Join with Oracle Server Style
Syntax
SQL> SELECT EMPLOYEES.EMPLOYEE_ID, JOB_CODE
cont> FROM EMPLOYEES, CURRENT_JOB
cont> WHERE EMPLOYEES.EMPLOYEE_ID= CURRENT_JOB.EMPLOYEE_ID(+);
EMPLOYEES.EMPLOYEE_ID CURRENT_JOB.JOB_CODE
00164 DMGR
00165 ASCK
00166 DMGR
00167 APGM
00168 DMGR
00169 SPGM
00170 SCTR
00171 PRGM
.
.
.