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
1 – 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> -----------------------+ |
+---------------- <----------------------------+
3 – Arguments
3.1 – 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.2 – 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.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.4 – 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.5 – 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.6 – 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.7 – 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.8 – 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.9 – 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.10 – 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.11 – 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.12 – 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.13 – 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.14 – 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.15 – 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.16 – 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.17 – 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.18 – 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.19 – 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.20 – 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.21 – 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.22 – 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.23 – 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.24 – 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.25 – 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.26 – 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.27 – 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.28 – 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.29 – 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.30 – 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.31 – SELECT_ALL
Specifies that duplicate rows should not be eliminated from the
result table. ALL is the default.
3.32 – SELECT_DISTINCT
Specifies that SQL should eliminate duplicate rows from the
result table.
3.33 – 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.34 – 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.35 – 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.36 – 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.37 – 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.38 – 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