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 (B)0select-expr =        qwqwq> select-clause qqqqqqqqqqqqwqwqqqqqqk    x tq> ( select-expr ) qqqqqqqqqqqu x  x      x mq> TABLE table-ref qqqqqqqqqqj x x     mqqqqqq select-merge-clause <qqqqqqqj   x  lqqqqqqqqqqqqqqqqqqq     mq> order-by-clause qj mq> offset-clause qj  mq> limit-to-clause qj (B)0select-merge-clause =    qwq> EXCEPT qwqqqqqqqqqqqqqqwqqqwqqqqwqqqqqqqqqqqqqqqqqqqwqq>  x   mq> DISTINCT qqj   x  tq> CORRESPONDING qqu  tq> INTERSECT qwqqqqqqqqqqqqqwqu   mq> NATURAL qqqqqqqqj x mq> DISTINCT qj x tq> MINUS qqqqqqqqqqqqqqqqqqqqqu mq> UNION qqwqqqqqqqqqqqqqwqqqqj  tq> ALL qqqqqqu  mq> DISTINCT qj (B)0select-clause =    SELECT qwqqqqqqqqqqqqqwq> select-list qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk  tq> ALL qqqqqqu    x  mq> DISTINCT qj    x lqqqqqqqqqqqqqqqqqq FROM qwqq> table-ref qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk  mqqqqqq , qqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqwk  m> WHERE predicate qqqqqqqqqqqqqqqj m> GROUP BY qw> <column-name> qux    t> value-expr qqqqux mqqqqqqq ,qqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>  mq> HAVING predicate qj  (B)0select-list =    qwqwqqqq> * qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>   x mqwqq> value-expr qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqj x   x x mq> AS <name> qwqqqqqqqqqqqqqqqqqqqqqqwj x x   x x mq> edit-using-clause qj x x   x x x x   x mwq> <table-name> qqqqqqwq> . * qqqqqqqqqqqqqqqqqqqqqqqqqqqj x   x tq> <view-name> qqqqqqqu x   x mq> <correlation-name> j x   mqqqqqqqqqqqqqqqq ,  EDIT USING qwq> edit-string qqqwq>     mq> <domain-name> qj  (B)0table-ref =    qqwqq> <table-name> qqwqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   tqq> <view-name>  qqu mq> correlation-name-clause qqu   tqq> derived-table qj x   mqq> joined-table qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0derived-table =    qqqq> ( qwq> select-expr qqwq> ) qqqq>   mq> joined-table qj    (B)0joined-table =    qqwqqqqqqq> qualified-join qqqqqqwqqqq>   tqqqqqqq> cross-join qqqqqqqqqqu   mq> ( qq> joined-table qq> ) qqj    (B)0qualified-join =    qwq> table-ref qwqqqqqqqqqqqqqwqq> JOIN qq> table-ref qk   x m> join-type qj x   x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   x mwq> ON predicate qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqw>   x mq> USING qqqq> ( qw> <column-name> wq> ) qqqqqqj x   x mqqqqqq , table-ref q> NATURAL qwqqqqqqqqqqqqqwq> JOIN q> table-ref qj   m> join-type qj    (B)0cross-join =    qqq> table-ref qq> CROSS JOIN qq> table-ref qqq>    (B)0join-type =    qqwq> INNER qqqqqqqqqqqqqqqqqqwqq>   tq> LEFT qqqwqq> OUTER qqqqqj   tq> RIGHT qqu   mq> FULL qqqj    (B)0correlation-name-clause =    q> AS <correlation-name> wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mq> ( qwq> <name-of-column> qwq> ) qqj   mqqqqqqqq , ORDER BY qwwq> value-expr qwwqqqqqqqqqwqwqq>   xmq> <integer> qqqqjtq> ASC qqu x   x mq> DESC qj x   mqqqqqqqqqqqq , OFFSET skip-expression qwq> ROW qqwqq>   mq> ROWS qqj  (B)0limit-to-clause =    qw> LIMIT TO qwq> limit-expression qwqqqqqqqqqqqqqqqqqqqqqqqqqqqwwwqqqqqqqqqww> x x tq> OFFSET skip-expression quxtq> ROW qux x x mq> SKIP skip-expression qqjxmq> ROWS qjx x mq> skip-expression , limit-expression qqqqqqqqqqqj x x x m> FETCH qw> FIRST qwqqwqqqqqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqwq> ONLY qqqqqqqqj m> NEXT qqj mq> limit-expression qj tq> ROW qqu mq> ROWS qj (B)0optimize-clause =            qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>   mq> OPTIMIZE qqwqwq> FOR qwq> FAST FIRST qqqqqqqqwqqqqqqqqqqwqwqj   x x tq> TOTAL TIME qqqqqqqqu   x x  x x mq> SEQUENTIAL ACCESS qj x x  x tq> USING <outline-name> qqqqqqqqqqqqqqqqqqu x  x tq> WITH qwq> DEFAULT qqwq> SELECTIVITY qu x x x tq> SAMPLED qqu x x x x mq> AGGRESSIVE j x x  x mq> AS <query-name> qqqqqqqqqqqqqqqqqqqqqqqj x   mqqqqqqqqqqqqqqqq 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: - 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