1  Select_Expressions



   Using select expressions, you can define four types of result
   tables:

   o  Simple tables

   o  Aggregate tables

   o  Joined tables

   o  Derived tables

   A simple table result is generated from a single table or view
   and usually includes the special DBKEY column. The rows of such
   tables can be updated, if privileges allow.

   An aggregate table is a virtual table formed by the GROUP
   BY clause (allowing multi-row results) or an aggregate (or
   statistical) expression without allowing a GROUP BY (a single
   row result).

   A table reference is a base table, view, derived table, or a
   joined table.

   A derived table is a named virtual table that represents data
   obtained through the evaluation of a select expression. A derived
   table is named by the specified correlation name. References to a
   derived table and its columns can be made within the query using
   the correlation name. A derived table is similar to a view in
   that a view is also a virtual table represented by the select
   expression used to define it. Therefore, a derived table is like
   a view whose definition is specified within the FROM clause.

   A joined table is a virtual table that represents data obtained
   through the joining of two table references. The type of join
   between the two table references can be either CROSS, INNER
   JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN.
   You need to use the joined table syntax to specify an outer join
   operation.

   There are two types of joined tables:

   o  Qualified join

   o  Cross join
 

2  Environment

   You can use select expressions, by themselves or as part of other
   SQL statements, in interactive SQL or in host language programs.

   SQL evaluates the arguments in a select clause 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)

   After each of these clauses, SQL produces an intermediate result
   table that is used in evaluating the next clause. The optimizer
   finds the fastest way of doing this without changing the result.
 

2  Format

 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> +                                      |
  +---------------- , <----------------------------------------------+



 edit-using-clause =
 
 --> EDIT USING -+-> edit-string ---+->
                 +-> <domain-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  -+



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

 

2  Arguments
 

3  AS_name

   You can, optionally, give a column a name that might not
   otherwise be named using the AS clause. For example:

   SQL> SELECT JOB_CODE AS JOB,
   cont> MAXIMUM_SALARY - MINIMUM_SALARY AS RANGE
   cont> FROM JOBS
   cont> WHERE JOB_CODE LIKE 'S%';
    JOB                     RANGE
    SANL                 20000.00
    SCTR                 15000.00
    SPGM                 25000.00
   3 rows selected

   You can use asterisks (*)  as wildcards in a select list.

   To use delimited identifiers, you must specify the SQL99 or
   similar dialect, or use the SET QUOTING RULES statement for these
   dialects.
 

3  ASC

   Syntax options:

   ASC | DESC

   Determines whether the values for sort keys are sorted in
   ascending or descending order.

   If you do not specify the sort order for the sort key, the
   default order is ascending.

   If your dialect is set to 'SQLV40' (the default dialect) then the
   sort order is inherited from the preceding sort key.
 

3  correlation-name-clause

   You can specify a correlation name following a table or a view,
   and you must specify a correlation name for a derived table in
   the FROM clause to qualify column names in other parts of the
   select expression. If you do not explicitly specify a correlation
   name, SQL implicitly specifies the table name or view name as a
   correlation name. The same correlation name may not be specified
   more than once, either explicitly or implicitly.

   The correlation name may also rename columns when specified with
   a derived table. Therefore, the number of columns in the table to
   the left of the correlation name must match the number of columns
   specified to the right of the correlation name.
 

3  CORRESPONDING

   The UNION, EXCEPT, MINUS, and INTERSECT operators can be followed
   by the keyword CORRESPONDING. This causes the two select lists of
   the select-merge-clause to be compared by name. Only those column
   names which appear in both lists are retained for the resulting
   query table.

   The name is either the column name, or the name provided by
   the AS clause. If there are no names in common, or a column
   name appears more than once in a select list then an error is
   reported.
 

3  CROSS_JOIN

   Combines all rows of the left-specified table reference to all
   rows of the right-specified table reference in the result. A
   cross join is a Cartesian product between two table references. A
   cross join is similar to the basic join expression but without
   the WHERE clause. This is also called a Cartesian product.
   Following is an example of the basic join expression using the
   comma-separated syntax:

   SQL> SELECT *
   cont>   FROM TABLE1, TABLE2;

   Using the CROSS JOIN clause, the previous example would appear as
   follows:

   SQL> SELECT *
   cont>   FROM TABLE1 CROSS JOIN TABLE2;
 

3  EDIT_USING

   Syntax options:

      EDIT USING edit-string
      EDIT USING domain-name

   Associates an edit string with a value expression. When a domain-
   name is specified, the edit string defined for that domain is
   used. This clause overrides any EDIT STRING defined for the
   columns or variables in the query. This clause is valid in
   interactive SQL only.
 

3  EXCEPT

   Syntax options:

   EXCEPT | EXCEPT DISTINCT



   The EXCEPT DISTINCT operator is used to create a result table
   from the first select expression except for those row values that
   also occur in the second select expression.

   DISTINCT is the default so EXCEPT and EXCEPT DISTINCT are
   identical operations. EXCEPT conforms to the ANSI and ISO
   SQL:1999 Database Language Standard.

                                  NOTE

      EXCEPT is not commutative. That is, A EXCEPT B may result in
      a different set of rows from B EXCEPT A.
 

3  FETCH

   Syntax options:

      FETCH FIRST limit-expression
      FROM NEXT limit-expression

The FETCH FIRST clause allows the database programmer to limit the
results returned from a query expression. The FETCH FIRST clause
is equivalent to functionality currently supported by the LIMIT TO
clause. FETCH accepts a numeric value expression which may contain
arbitrary arithmetic operators, function calls, subselect clauses or
sequence references. The subselect clauses may not reference columns
in the outer query as it is evaluated before row processing begins.

   The FETCH NEXT is identical to FETCH FIRST but allows the syntax
   to be more descriptive when coupled with the OFFSET clause.

   If no value expression is provided for FETCH it will default to 1
   row.

   The FETCH clause is not compatible with the LIMIT TO clause.

   The following example uses the FETCH FIRST to find the oldest
   manager in the company. The example uses the DEPARTMENTS table
   to locate the employee id of each manager, and after sorting them
   by their birthday, the oldest manager's name and employee id are
   displayed.

   SQL> -- select the most senior manager
   SQL> select e.last_name, e.first_name, e.employee_id
   cont> from departments d, employees e
   cont> where d.manager_id = e.employee_id
   cont> order by e.birthday
   cont> fetch first row only;
    E.LAST_NAME      E.FIRST_NAME   E.EMPLOYEE_ID
    O'Sullivan       Rick           00190
   1 row selected
   SQL>
 

3  FROM_derived_table

   A derived table is a named virtual table containing data obtained
   through the evaluation of the select expression in the FROM
   clause. The derived table is named by specifying the correlation
   name.

   You must specify a correlation name for a derived table. This
   may determine which column names the user can specify in the
   select list or subsequent clauses. The select list and subsequent
   clauses can reference only the correlation name and the column
   names of the derived table and cannot reference the table or
   column names that defined the derived table.

   Following is an example of a derived table using the personnel
   database. This example finds all departments that have less than
   3 rows in the JOB_HISTORY table.

   SQL> SELECT *
   cont> FROM (SELECT DEPARTMENT_CODE, COUNT(*)
   cont>       FROM JOB_HISTORY
   cont>       WHERE JOB_END IS NULL
   cont>       GROUP BY DEPARTMENT_CODE)
   cont>       AS DEPT_INFO (D_CODE, D_COUNT)
   cont> WHERE D_COUNT < 3;
    D_CODE       D_COUNT
    ENG                2
    MCBS               1
    MSMG               1
    MTEL               2
    PERS               2
    SUSA               2
   6 rows selected
 

3  FROM_joined_table

   A joined table represents a join between two table references
   specified in the FROM clause.

   There are two types of joined tables:

   o  Qualified join-syntax contains either an implicit or explicit
      predicate

   o  Cross join-syntax does not contain a predicate

   A table can be joined to itself or joined to other tables. When
   an outer join is specified in the joined-table expression, you
   can use the parentheses to explicitly define the join order.
   If only inner or cross joins are specified in the joined-table
   expression, the use of parentheses does not affect the join
   order. SQL tries all possible join orders to find the most
   efficient order for the query. If outer joins are specified in
   the joined-table expression, the join order is determined first
   by any existing parentheses and then by the left-to-right rule.

   The table or correlation names specified in the joined-table
   expression can be referenced by the outer select expression.
 

3  FROM_name

   Syntax options:

      FROM table-name
      FROM view-name

   Identifies the tables and views that SQL uses to generate the
   result table. If you name more than one table or view, SQL joins
   them to create an intermediate result table.
 

3  FULL_OUTER_JOIN

   Preserves all rows from the left-specified table reference and
   all rows from the right-specified table reference in the result.
   NULL appears in any column that does not have a matching value in
   the corresponding column. For example:

   SQL> SELECT *
   cont> FROM TABLE1 FULL OUTER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1;
      TABLE1.C1     TABLE1.C2     TABLE2.C1   TABLE2.C4
             10            15            10   AA
           NULL          NULL            15   BB
             20            25            20   CC
             30            35          NULL   NULL
   4 rows selected

   You must specify at least one equijoin condition in the ON clause
   of a FULL OUTER JOIN clause. This restriction does not apply to
   a FULL OUTER JOIN clause with the USING clause or to the NATURAL
   FULL OUTER JOIN clause.

   An equijoin matches values in columns from one table with the
   corresponding values of columns in another table implicitly using
   an equal (=)  sign.
 

3  GROUP_BY_value-expr

   Indicates the value expressions that SQL uses for organizing the
   intermediate result table from the WHERE clause, if specified, or
   the FROM clause. These groups of rows containing the same value
   are also called control breaks.

   For the first expression specified in the GROUP BY clause, SQL
   orders the rows of the preceding intermediate result table into
   groups whose rows all have the same value for the specified
   expression. If a second expression is specified in the GROUP
   BY clause, SQL then groups rows within each main group by values
   of the second expression. SQL groups any additional columns in
   the GROUP BY clause in a similar manner.

   All null values for a column name in the GROUP BY clause are
   grouped together.

   Each group is treated as the source for the values of a single
   row of the result table.

   Because all rows of a group have the same value for the value
   expression specified in the GROUP BY clause, references within
   value expressions or predicates to that column specify a single
   value.
 

3  HAVING_predicate

   Specifies a predicate that SQL evaluates to generate an
   intermediate result table. SQL evaluates the predicate for each
   group of the intermediate result table created by a preceding
   clause. The groups of that table for which the predicate is true
   become another intermediate result table to which SQL applies the
   select list for evaluation.

   If the clause preceding the HAVING clause is a GROUP BY
   clause, then the predicate is evaluated for each group in the
   intermediate result table. The HAVING clause affects groups just
   as the WHERE clause affects individual rows.

   If the HAVING clause is not preceded by a GROUP BY clause, SQL
   evaluates the predicate for all the rows in the intermediate
   result table as a single group.

   SQL restricts which expressions you can specify in the predicate
   of a HAVING clause. A column name or expression in a HAVING
   predicate must meet one of the following criteria:

   o  It must also appear in the GROUP BY clause.

   o  It must be specified within an aggregate function.

   o  It must be an outer reference (possible only if the HAVING
      clause is part of a column select expression).

   For instance, the following statement is invalid. It has a
   HAVING clause without a GROUP BY clause, which means that any
   column names in the HAVING clause must be part of a function
   (because there is no outer query, the column names cannot be
   outer references).

   SQL> SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES
   cont> HAVING FIRST_NAME = 'Bob';
   %SQL-F-NOTGROFLD, Column FIRST_NAME cannot be referred to in
   the select list or HAVING clause because it is not in the GROUP BY clause
 

3  INNER_JOIN

   Combines all rows of the left-specified table reference to
   matching rows in the right-specified table reference. For
   example:

   SQL> SELECT *
   cont> FROM TABLE1 INNER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1
   cont> AND C2 BETWEEN 25 AND 35;
      TABLE1.C1     TABLE1.C2     TABLE2.C1   TABLE2.C4
             10            15            10   AA
             20            25            20   CC
   2 rows selected

   Both TABLE1 and TABLE2 are exposed in the remainder of the select
   clause and, therefore, can be used to qualify columns from either
   table reference.

   SQL> SELECT *
   cont> FROM TABLE1 INNER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1
   cont> WHERE TABLE1.C1 = 10;
      TABLE1.C1     TABLE1.C2     TABLE2.C1     TABLE2.C4
             10            15            10            AA
   1 row selected

   If INNER JOIN is specified in the joined-table expression, it
   implies any join ordering of the table references. For example, A
   INNER JOIN B INNER JOIN C is equivalent to A INNER JOIN C INNER
   JOIN B. In general, any permutation of table references A, B,
   and C in an inner join table expression produces the same result.
   Further, SELECT * FROM A INNER JOIN B ON P1 INNER JOIN C ON P2 is
   equivalent to the syntax SELECT * FROM A, B, C WHERE P1 AND P2.
 

3  INTERSECT

   Syntax options:

   INTERSECT | INTERSECT DISTINCT

   The INTERSECT DISTINCT operator is used to create a result table
   from the first select expression for those row values that also
   occur in the second select expression.

   DISTINCT is the default so INTERSECT and INTERSECT DISTINCT are
   identical operations. INTERSECT conforms to the ANSI and ISO
   SQL:1999 Database Language Standard.

                                  NOTE

      In general INTERSECT is commutative. That is, A INTERSECT B
      results in the same set of rows from B INTERSECT A. This is
      demonstrated by the examples below. However, care should be
      taken when using LIMIT TO within the different branches of
      the INTERSECT as this will make the result non deterministic
      because of possible different solution strategies employed
      by the Rdb optimizer.
 

3  LEFT_OUTER_JOIN

   Preserves all rows in the left-specified table reference and
   matches to rows in the right-specified table reference in the
   result. NULL appears in columns where there is no match in the
   right-specified table. For example:

   SQL> SELECT *
   cont> FROM TABLE1 LEFT OUTER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1;
      TABLE1.C1     TABLE1.C2     TABLE2.C1   TABLE2.C4
             10            15            10   AA
             20            25            20   CC
             30            35          NULL   NULL
   3 rows selected

   Basically, outer joins are an inner join with a union adding NULL
   to all unmatched rows. Notice that the LEFT OUTER JOIN example
   results are the same as the INNER JOIN example results plus the
   unmatched row.

   The search condition specified in the ON clause is used to
   construct the outer join result. In addition to the join
   predicates, you can specify selection predicates and subqueries
   in the ON clause. For example:

   SQL> SELECT *
   cont> FROM TABLE1 LEFT OUTER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1
   cont> AND C2 BETWEEN 25 AND 35;
      TABLE1.C1     TABLE1.C2     TABLE2.C1   TABLE2.C4
             10            15          NULL   NULL
             20            25            20   CC
             30            35          NULL   NULL
   3 rows selected

   A select condition in the ON clause reduces the inner join
   result. The left outer join result contains the inner join result
   plus each row from TABLE1 that did not match a row in TABLE2 and
   was extended with NULL.

   In contrast, the result from the following example uses the same
   selection condition but with the WHERE clause:

   SQL> SELECT *
   cont> FROM TABLE1 LEFT OUTER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1
   cont> WHERE C2 BETWEEN 25 AND 35;
      TABLE1.C1     TABLE1.C2     TABLE2.C1   TABLE2.C4
             20            25            20   CC
             30            35          NULL   NULL
   2 rows selected

   In the previous example, the left outer join result is first
   constructed using the search condition in the ON clause. Then the
   selection condition in the WHERE clause is applied to each row in
   the outer join result to form the final result.
 

3  LIMIT_TO

      LIMIT TO limit-expression
      LIMIT TO skip-expression

   The LIMIT TO clause allows you to limit the number of rows
   in the result table, or to skip rows returned from a query.
   For example, the first row in the result set might be the
   column headings loaded from a CSV data source loaded by the
   RMU/LOAD/RECORD=FORMAT=DELIMITED command that should be ignored
   by queries.

   If either limit-expression or skip-expression is specified as a
   numeric literal, then it must be an unscaled value. These numeric
   expressions are converted to BIGINT before executing the query.

   Neither limit-expression nor skip-expression can reference
   columns from the select-expression in which they occur. You
   can use only columns of a subselect specified for the limit-
   expression or skip-expression. The example in this section uses a
   subselect in the LIMIT TO clause.

                                  NOTE

      Oracle recommends that the values specified for skip-
      expression be kept small for performance reasons. The
      skipped rows are still fetched and processed by the query;
      they are just not returned to the application.

   If limit-expression is evaluated to a negative value or zero,
   then no rows are returned from the query, and no error is
   reported.

   If skip-expression is evaluated to a negative value or zero, then
   no rows are skipped. If the skip-expression is larger than the
   rows in the result set, then no rows are returned from the query,
   and no error is reported. The following examples show the use of
   the LIMIT TO ... SKIP syntax.

   This query returns the 100th employee from the EMPLOYEES table:

   SQL> select last_name, first_name, employee_id
   cont> from employees
   cont> order by employee_id
   cont> limit to 1 skip 99 rows;
    LAST_NAME        FIRST_NAME   EMPLOYEE_ID
    Herbener         James        00471
   1 row selected

   To retrieve the last row in the sorted list, you can replace the
   literal value with a subselect that calculates the value as shown
   in the following example. This query also shows the output from
   the SET FLAGS command for the query strategy.

   SQL> set flags 'strategy,detail';
   SQL> select last_name, first_name, employee_id
   cont> from employees
   cont> order by employee_id
   cont> limit to 1
   cont> skip (select count(*)-1 from employees) rows;
   Tables:
     0 = EMPLOYEES
     1 = EMPLOYEES
   Cross block of 2 entries
     Cross block entry 1
       Aggregate: 0:COUNT (*)
       Index only retrieval of relation 1:EMPLOYEES
         Index name  EMP_EMPLOYEE_ID [0:0]
     Cross block entry 2
       Firstn: 1
       Skipn: <agg0> - 1
       Get     Retrieval by index of relation 0:EMPLOYEES
         Index name  EMP_EMPLOYEE_ID [0:0]
    LAST_NAME        FIRST_NAME   EMPLOYEE_ID
    Herbener         James        00471
   1 row selected
   SQL>

   An alternative to this query would be to use ORDER ... DESC and
   then to use a LIMIT 1 ROW clause.

   This query finds the statistical median salary:

   SQL> -- select the median salary
   SQL> select salary_amount
   cont> from salary_history
   cont> where salary_end is NULL
   cont> order by salary_amount
   cont> limit to 1
   cont> skip (select count(*)/2
   cont>       from salary_history
   cont>       where salary_end is NULL);
    SALARY_AMOUNT
       $24,166.00
   1 row selected
   SQL>

   This result can be compared with the average salary:

   SQL> -- select the median salary compare with average
   SQL> select salary_amount as median_salary,
   cont>        (select avg (salary_amount)
   cont>         from salary_history
   cont>         where salary_end is NULL) as avg_salary edit using SALARY
   cont> from salary_history
   cont> where salary_end is NULL
   cont> order by salary_amount
   cont> limit to 1
   cont> skip (select count(*)/2
   cont>       from salary_history
   cont>       where salary_end is NULL);
    MEDIAN_SALARY    AVG_SALARY
       $24,166.00    $31,922.79
   1 row selected
   SQL>
 

3  MINUS

   The MINUS operator is a synonym for the EXCEPT DISTINCT operator
   and is provided for language compatibility with the Oracle RDBMS
   SQL language.
 

3  NATURAL_JOIN

   Performs an equijoin operation on the matching named columns of
   the specified tables. An equijoin matches values in columns from
   one table with the corresponding values of columns in another
   table implicitly using an equal (=)  sign.

   A NATURAL JOIN implicitly performs the following functions:

   o  Coalesces the common columns to condense the columns into a
      single column and, therefore, you cannot qualify the common
      column

   o  Performs an equijoin using common columns between table
      references

   You cannot specify an explicit join condition if the NATURAL
   keyword is specified in the query. Following is an example of a
   natural join. Note the common column C1 is only shown once. Other
   types of join conditions return the common column as often as it
   occurs in the table's references.

   SQL> SELECT *
   cont> FROM TABLE1 NATURAL LEFT OUTER JOIN TABLE2;
             C1     TABLE1.C2   TABLE2.C4
             10            15          AA
             20            25          CC
             30            35        NULL
   3 rows selected

   The complexity of what the NATURAL LEFT OUTER JOIN is implicitly
   executing in the previous example is shown in the following
   example:

   SQL> SELECT
   cont>   COALESCE (TABLE1.C1, TABLE2.C1) AS C1,
   cont>   TABLE1.C2, TABLE2.C4
   cont> FROM TABLE1 LEFT OUTER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1;
             C1     TABLE1.C2   TABLE2.C4
             10            15          AA
             20            25          CC
             30            35        NULL
   3 rows selected

   The NATURAL keyword can be specified for INNER, LEFT OUTER, RIGHT
   OUTER, and FULL OUTER joins.

   A natural join between two table references that do not share
   matching named columns results in a Cartesian product.
 

3  OFFSET

   The OFFSET clause allows the database programmer to start
   fetching the result rows from the specified offset within the
   result table. OFFSET accepts a numeric value expression which may
   contain arbitrary arithmetic operators, function calls, subselect
   clauses or sequence references. The subselect clauses may not
   reference columns in the outer query as it is evaluated before
   row processing begins.

   The OFFSET clause is equivalent in functionality to the
   SKIP clause currently supported by the LIMIT TO clause. The
   distinction is that OFFSET can be specified without a row limit.

   This following query uses a subselect in the OFFSET clause to
   locate the median (or middle) row of the sorted set.

   SQL> select e.last_name, e.first_name, employee_id, sh.salary_amount
   cont> from salary_history sh inner join employees e using (employee_id)
   cont> where sh.salary_end is null
   cont> order by sh.salary_amount
   cont> offset (select count(*)
   cont>         from salary_history
   cont>         where salary_end is null)/2 rows
   cont> fetch next row only;
    E.LAST_NAME      E.FIRST_NAME   EMPLOYEE_ID   SH.SALARY_AMOUNT
    Boyd             Ann            00244               $24,166.00
   1 row selected
   SQL>
 

3  ON_predicate

   Specifies a search condition on which the join is based. The
   predicate can have columns from the two operands mentioned, or
   have outer references if it is in a subquery.
 

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  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  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  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  ORDER_BY

   Syntax options:

      ORDER BY integer
      ORDER BY value-expr

   Specifies the order of rows for the result table. SQL sorts
   the rows from the intermediate result table by the values of
   expressions specified in the ORDER BY clause. The intermediate
   result table is the result table SQL produces when it evaluates
   the preceding clause in the select expression (HAVING, GROUP BY,
   WHERE, or FROM).

   You can refer to columns in the ORDER BY clause in two ways:

   o  By a value expression

   o  By column number, where the integer you specify indicates the
      left-to-right position of the column in the result table

   You must use an integer to identify a column in the ORDER BY
   clause if that column in the select list is derived from a
   function, an arithmetic expression, or the result of a UNION,
   MINUS, EXCEPT, or INTERSECT operator.

   Whether you identify expressions in an ORDER BY clause using a
   name or using a number, the expressions are called sort keys.

   When you use multiple sort keys, SQL treats the first expression
   as the major sort key and successive keys as minor sort keys.
   That is, it first sorts the rows into groups based on the first
   value expression. Then, it uses the second value expression to
   sort the rows within each group, and so on. Unless you specify
   a sort key for every column in the result table, rows with
   identical values for the last sort key specified will be in
   arbitrary order.

   The following example illustrates 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
      .
      .
      .
 

3  qualified-join

   Qualifies and alters the result returned from the joined tables.
   There are several types of qualified joins:

   o  INNER JOIN

   o  LEFT OUTER JOIN

   o  RIGHT OUTER JOIN

   o  FULL OUTER JOIN

   o  NATURAL JOIN

   For an INNER and OUTER JOIN, the result table is the combination
   of all columns of the first table reference to all the columns in
   the second table reference. For a NATURAL JOIN, the result table
   condenses common columns (that is, columns with the same name)
   between the table references.
 

3  RIGHT_OUTER_JOIN

   Preserves all rows of the right-specified table reference and
   matches to rows in the left-specified table reference in the
   result. NULL appears in columns where there is no match in the
   left-specified table reference. For example:

   SQL> SELECT *
   cont> FROM TABLE1 RIGHT OUTER JOIN TABLE2
   cont> ON TABLE1.C1 = TABLE2.C1;
      TABLE1.C1     TABLE1.C2     TABLE2.C1   TABLE2.C4
             10            15            10   AA
           NULL          NULL            15   BB
             20            25            20   CC
   3 rows selected

   Notice that the FULL OUTER JOIN example result is the same as the
   INNER JOIN example result plus the unmatched rows from TABLE1 and
   unmatched rows from TABLE2.
 

3  SELECT_*_(wildcard_character)

   Tells SQL to use all the column results from the intermediate
   result table (namely, all the columns in all the table references
   referred to in the FROM clause). If the select expression
   contains a GROUP BY clause, SQL interprets the wildcard (*)  as
   specifying only the expressions in the GROUP BY clause.
 

3  SELECT_ALL

   Specifies that duplicate rows should not be eliminated from the
   result table. ALL is the default.
 

3  SELECT_DISTINCT

   Specifies that SQL should eliminate duplicate rows from the
   result table.
 

3  SELECT_name.*

   Tells SQL to use all the columns in the table references referred
   to by the table name, view name, or correlation name. The name
   must be specified in the FROM clause of the select expression.
   You cannot mix this form of wildcard notation with SELECT *.

   The number of columns you specify in the select list, either by
   using wildcards or by explicitly listing value expressions, is
   the number of columns in the result table. In
 

3  select-list

   Identifies a list of value expressions (to be derived from the
   table references named in the FROM clause) for the final result
   table.
 

3  UNION

   Syntax options:

   UNION | UNION DISTINCT

   Merges the results of a select expression with another select
   expression into one result table by appending the values of
   columns in one table with the values of columns in other tables.

   The following example extracts the EMPLOYEE_ID of current
   employees with a salary greater than $50,000 and with a Ph.D.
   Duplicate rows are eliminated from the result table:

   SQL> SELECT EMPLOYEE_ID
   cont>   FROM CURRENT_SALARY
   cont>   WHERE SALARY_AMOUNT > 50000
   cont> UNION
   cont>   SELECT EMPLOYEE_ID
   cont>     FROM DEGREES
   cont>     WHERE DEGREE = 'PhD';
    EMPLOYEE_ID
    00164
    00166
    00168
    00169
    00172
    00182
      .
      .
      .
    00418
    00435
    00471
   38 rows selected
 

3  UNION_ALL

   Specifies that duplicate rows should not be eliminated from the
   result table. By default, the UNION operator removes duplicate
   rows.

   The following example returns duplicate rows from the result
   table:

   SQL> SELECT LAST_NAME, SEX FROM EMPLOYEES WHERE LAST_NAME = 'Nash'
   cont> UNION ALL
   cont> SELECT LAST_NAME, SEX FROM EMPLOYEES WHERE LAST_NAME = 'Lapointe';
    LAST_NAME        SEX
    Nash             M
    Nash             M
    Lapointe         F
    Lapointe         F
   4 rows selected
 

3  USING

   Specifies the columns on which the join is based. Column names
   must be defined in both table references specified in the
   qualified join. The USING clause implies an equijoin condition
   between columns of the same name and creates a common column in
   the result.

   SQL> SELECT *
   cont> FROM TABLE1 LEFT OUTER JOIN TABLE2
   cont> USING (C1);
             C1     TABLE1.C2   TABLE2.C4
             10            15          AA
             20            25          CC
             30            35        NULL
   3 rows selected

   The common columns are coalesced into a single column in the
   result in the previous example. Therefore, such columns cannot be
   qualified. You can reference the coalesced column in a query. For
   example:

   SQL> SELECT *
   cont> FROM TABLE1 LEFT OUTER JOIN TABLE2
   cont> USING (C1)
   cont> WHERE C1 BETWEEN 20 AND 30;
             C1     TABLE1.C2   TABLE2.C4
             20            25          CC
             30            35        NULL
   2 rows selected
 

3  WHERE_predicate

   Specifies a predicate that SQL evaluates to generate an
   intermediate result table. SQL evaluates the predicate for each
   row of the intermediate result table created by the FROM clause.
   The rows of that table for which the predicate is true become
   another intermediate result table for later clauses in a select
   expression.

   Column names specified in the predicate of the WHERE clause must
   either:

   o  Identify columns of the intermediate result table created by
      the FROM clause.

   o  Be an outer reference (possible only if the WHERE clause is
      part of a column select expression). See Outer_References for
      more information on outer references.

   In general, the predicate in a WHERE clause cannot refer to an
   aggregate function. For instance, the following statement is
   invalid:

   SQL> SELECT * FROM EMPLOYEES WHERE MAX(LAST_NAME) > 'X';
   %SQL-F-INVFUNREF, Invalid function reference