1  SELECT
 

2  General_Form
   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.
 

3  Environment
   You can use the general form of the SELECT statement only in
   interactive and dynamic SQL.
 

3  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
 

4  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.
 

4  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.
 

4  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
 

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.
 

4  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.
 

4  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).
 

4  select-expr

   See the Select_Expressions HELP topic for a detailed description
   of select expressions.
 

3  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
      .
      .
      .
 

2  Singleton_Select
   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. A singleton select
   statement specifies a one-row result table, and is allowed in
   either precompiled programs or as part of a procedure in an SQL
   module. A singleton select includes an additional clause, INTO,
   to assign the values in the row to host language variables in a
   program.

   For information on the general form of the SELECT statement, see
   the SELECT General_Form statement.
 

3  Environment
   You can use a singleton select statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

3  Format

 singleton-select =
 
 SELECT -+-------------+-> select-list --+
         +-> ALL ------+                 |
         +-> DISTINCT -+                 |
   +-------------------------------------+
   +-> into-target -----------------+
   +--------------------------------+
   +> FROM -+--> table-ref -----+-+
            +---------- , <-----+ |
   +------------------------------+
   +> WHERE predicate -+------------->-----------------++
                       +-> GROUP BY -+> <column-name> ++|
                                     +------- , <-----+ |
   +--------------------------<-------------------------+
   ++--------------------+-+-----------------------+----+
    +> HAVING predicate -+ +-> limit-to-clause ----+    |
   +----------------------------------------------------+
   +-+---------------------+-+---------------------+------>
     +> for-update-clause -+ +-> optimize-clause --+



 into-target =
 ----> INTO --+-+-> <parameter> -----------++->
              | +-> <qualified-parameter> -+|
              | +-> <variable> ------------+|
              +------------- , <------------+
 



 for-update-clause =
 
 --> FOR UPDATE -+-------------------------+->
                 +-+-> OF <column-name> -+-+
                   +-------- , <---------+
 



 optimize-clause =
 
 --+---------------------------------------------------------------+--->
   +-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
                  | |        +-> TOTAL TIME --------+          | |
                  | |        +-> SEQUENTIAL ACCESS -+          | |
                  | +-> USING <outline-name> ------------------+ |
                  | +-> WITH -+-> DEFAULT  --+-> SELECTIVITY  -+ |
                  | |         +-> SAMPLED  --+                 | |
                  | |         +-> AGGRESSIVE +                 | |
                  | +-> AS <query-name> -----------------------+ |
                  +---------------- <----------------------------+
 

 

3  Arguments
 

4  INTO

   Syntax options:

      INTO parameter
      INTO qualified-parameter
      INTO variable

   Specifies a list of parameters, qualified parameters
   (structures), or variables to receive values from the columns
   of the one-row result table. The variables named must have been
   declared in the host program. If a variable named in the list
   is a host structure, SQL considers the reference the same as a
   reference to each of the elements of the host structure.

   If the number of variables specified, either explicitly or by
   reference to a host structure, does not match the number of
   values in the row of the result table, SQL generates an error
   when it precompiles the program or compiles the SQL module file.

   If columns in the result table from a singleton select include
   null values, the corresponding parameters must include indicator
   parameters.
 

4  select-list

   For a description of select lists, see the Select_Expressions
   HELP topic.