SELECT

The SELECT statement retrieves data from one or more tables. The retrieved data is presented in the form of a table that is called the result table, result set, or query result.

Required privilege

No privilege is required for the object owner.

SELECT for another user's object.

SELECT...FOR UPDATE also requires UPDATE privilege for another user's object.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

The general syntax for a SELECT statement is the following:

[WithClause] SELECT [hint][FIRST NumRows | ROWS m TO n] [ALL | DISTINCT] SelectList
  FROM TableSpec [,...]
  [WHERE SearchCondition]
  [GROUP BY GroupByClause [,...] [HAVING SearchCondition]]
  [ORDER BY OrderByClause [,...]]
  [FOR UPDATE [OF [[Owner.]TableName.]ColumnName [,...]] 
    [NOWAIT | WAIT Seconds]]

The syntax for a SELECT statement that contains the set operators UNION, UNION ALL, MINUS, or INTERSECT is as follows:

SELECT [hint] [ROWS m TO n] [ALL] SelectList
  FROM TableSpec [,...]
    [WHERE SearchCondition]
    [GROUP BY GroupByClause [,...] [HAVING SearchCondition] [,...]]
  {UNION [ALL] | MINUS | INTERSECT}
  SELECT [ROWS m TO n] [ALL] SelectList
    FROM TableSpec [,...]
     [WHERE SearchCondition]
     [GROUP BY GroupByClause [,...] [HAVING SearchCondition [,...] ] ]
     [ORDER BY OrderByClause [,...] ]

The syntax for OrderByClause is as follows:

{ColumnID|ColumnAlias|Expression} [ASC|DESC] [NULLS { FIRST|LAST }]

Parameters

Parameter	Description
[WithClause]	The WITH clause, also known as subquery factoring, enables you to assign a name to a subquery block, which can subsequently be referenced multiple times within the top-level SELECT statement. See "WithClause" for information on the syntax for the WithClause.
hint	Specifies a statement level optimizer hint for the SELECT statement. See "Statement Level Optimizer Hints" for information on statement level optimizer hints.
FIRST NumRows	Specifies the number of rows to retrieve. NumRows must be either a positive INTEGER value or a dynamic parameter placeholder. The syntax for a dynamic parameter placeholder is either ? or :DynamicParameter. The value of the dynamic parameter is supplied when the statement is executed.
ROWS m TO n	Specifies the range of rows to retrieve where m is the first row to be selected and n is the last row to be selected. Row counting starts at row 1. The query SELECT ROWS 1 TO n returns the same rows as SELECT FIRST NumRows assuming the queries are ordered and n and NumRows have the same value. Use either a positive INTEGER value or a dynamic parameter placeholder for m and n values. The syntax for a dynamic parameter placeholder is either ? or :DynamicParameter. The value of the dynamic parameter is supplied when the statement is executed.
ALL	Prevents elimination of duplicate rows from the query result. If neither ALL nor DISTINCT is specified, ALL is the default.
DISTINCT	Ensures that each row in the query result is unique. All NULL values are considered equal for this comparison. Duplicate rows are not evaluated. You cannot use SELECT ... on a LOB column.
SelectList	Specifies how the columns of the query result are to be derived. See "SelectList" for the syntax for a select list.
FROM TableSpec	Identifies the tables referenced in the SELECT statement. The maximum number of tables per query is 24. TableSpec identifies a table from which rows are selected. The table can be a derived table, which is the result of a SELECT statement in the FROM clause. See "TableSpec" for the TableSpec syntax.
WHERE SearchCondition	The WHERE clause determines the set of rows to be retrieved. Normally, rows for which SearchCondition is FALSE or NULL are excluded from processing, but SearchCondition can be used to specify an outer join in which rows from an outer table that do not have SearchCondition evaluated to TRUE with respect to any rows from the associated inner table are also returned, with projected expressions referencing the inner table set to NULL. The unary (+) operator may follow some column and ROWID expressions to indicate an outer join. The (+) operator must follow all column and ROWID expressions in the join conditions that refer to the inner table. There are several conditions on the placement of the (+) operator. These generally restrict the type of outer join queries that can be expressed. The (+) operator may appear in WHERE clauses but not in HAVING clauses. Two tables cannot be outer joined together. An outer join condition cannot be connected by OR. See "Search Conditions" for more information on search conditions.
GROUP BY GroupByClause [,...]	The GROUP BY clause identifies one or more expressions to be used for grouping when aggregate functions are specified in the select list and when you want to apply the function to groups of rows. See "GROUP BY Clause" for information on the syntax for the GROUP BY clause.
HAVING SearchCondition	The HAVING clause can be used in a SELECT statement to filter groups of an aggregate result. The existence of a HAVING clause in a SELECT statement turns the query into an aggregate query. All columns referenced outside the sources of aggregate functions in any clause except the WHERE clause must be included in the GROUP BY clause. Subqueries can be specified in the HAVING clause.
(+)	A simple join (also called an inner join) returns a row for each pair of rows from the joined tables that satisfy the join condition specified in SearchCondition. Outer joins are an extension of this operator in which all rows of the outer table are returned, whether or not matching rows from the joined inner table are found. In the case no matching rows are found, any projected expressions referencing the inner table are given the value NULL.
ORDER BY OrderByClause [,...]	Sorts the query result rows in order by specified columns or expressions. Specify the sort key columns in order from major sort key to minor sort key. The ORDER BY clause supports column aliases, which can be referenced only in an ORDER BY clause. A single query may declare several column aliases with the same name, but any reference to that alias results in an error.
ColumnID	Must correspond to a column in the select list. You can identify a column to be sorted by specifying its name or its ordinal number. The first column in the select list is column number 1. It is better to use a column number when referring to columns in the select list if they are not simple columns. Some examples are aggregate functions, arithmetic expressions, and constants. A ColumnID in the ORDER BY clause has this syntax: {ColumnNumber |[[Owner.]TableName.] ColumnName}
ColumnAlias	Used in an ORDER BY clause, the column alias must correspond to a column in the select list. The same alias can identify multiple columns. {* | [Owner.]TableName.* | {Expression | [[Owner.]TableName.]ColumnName | [[Owner.]TableName.]ROWID } [[AS] ColumnAlias]} [,...]
ASC|DESC	For each column designated in the ORDER BY clause, you can specify whether the sort order is to be ascending or descending. If neither ASC (ascending) nor DESC (descending) is specified, ascending order is used. All character data types are sorted according to the current value of the NLS_SORT session parameter.
NULLS { FIRST|LAST }	Valid with ORDER BY clause and is optional. If you specify ASC or DESC, NULLS FIRST or NULLS LAST must follow ASC or DESC. Specify NULLS FIRST to have rows with NULL values returned first in your ordered query. Specify NULLS LAST to have rows with NULL values returned last in your ordered query. NULLS LAST is the default when rows are returned in ascending order. NULLS FIRST is the default when rows are returned in descending order. If you specify the ORDER BY clause without the ASC or DESC clause and without the NULLS FIRST or NULLS LAST clause, the default ordering sequence is ascending NULLS LAST.
FOR UPDATE [OF [[Owner.] TableName.] ColumnName [,...]] [NOWAIT | WAIT Seconds]	FOR UPDATE FOR UPDATE maintains a lock on a row until the end of the current transaction, regardless of isolation. All other transactions are excluded from performing any operation on that row until the transaction is committed or rolled back. FOR UPDATE may be used with joins and the ORDER BY, GROUP BY, and DISTINCT clauses. Update locks are obtained on either tables or rows, depending on the table/row locking method chosen by the optimizer. Rows from all tables that satisfy the WHERE clause are locked in UPDATE mode unless the FOR UPDATE OF clause is specified. This clause specifies which tables to lock. If using row locks, all qualifying rows in all tables from the table list in the FROM clause are locked in update mode. Qualifying rows are those rows that satisfy the WHERE clause. When table locks are used, the table is locked in update mode whether or not there are any qualifying rows. If the serializable isolation level and row locking are enabled, nonqualifying rows are downgraded to shared mode. If a read-committed isolation level and row locking are turned on, nonqualifying rows are unlocked. SELECT...FOR UPDATE locks are not blocked by SELECT locks. FOR UPDATE [OF [[Owner.]TableName.]ColumnName [,...] ] This mode optionally includes the name of the column or columns in the table to be locked for update. [NOWAIT | WAIT Seconds ] This specifies how to proceed if the selected rows are locked. It does not apply to table-level locks or database-level locks. NOWAIT specifies that there is no waiting period for locks. An error is returned if the lock is not available. WAIT Seconds specifies the lock timeout setting. An error is returned if the lock is not obtained in the specified amount of time. The lock timeout setting is expressed in seconds or fractions of second. The data type for Seconds is NUMBER. Values between 0.0 and 1000000.0 are valid. If neither NOWAIT nor WAIT is specified, the lock timeout interval for the transaction is used.
SelectQuery1 {UNION [ALL] | MINUS | INTERSECT} SelectQuery2	Specifies that the results of SelectQuery1 and SelectQuery2 are to be combined, where SelectQuery1 and SelectQuery2 are general SELECT statements with some restrictions. The UNION operator combines the results of two queries where the SelectList is compatible. If UNION ALL is specified, duplicate rows from both SELECT statements are retained. Otherwise, duplicates are removed. The MINUS operator combines rows returned by the first query but not by the second into a single result. The INTERSECT operator combines only those rows returned by both queries into a single result. The data type of corresponding selected entries in both SELECT statements must be compatible. One type can be converted to the other type using the CAST operator. Nullability does not need to match. The length of a column in the result is the longer length of correspondent selected values for the column. The column names of the final result are the column names of the leftmost select. You can combine multiple queries using the set operators UNION, UNION ALL, MINUS, and INTERSECT. One or both operands of a set operator can be a set operator. Multiple or nested set operators are evaluated from left to right. The set operators can be mixed in the same query. Restrictions on the SELECT statement that specify the set operators are as follows: Neither SELECT statement can specify FIRST NumRows. The SELECT subquery in a UNION, UNION ALL, MINUS, or INTERSECT must have the same number of projected expressions. This is true for INSERT...SELECT as well. ORDER BY can be specified to sort the final result but cannot be used with any individual operand of a set operator. Only column names of tables or column alias from the leftmost SELECT statement can be specified in the ORDER BY clause. GROUP BY can be used to group an individual SELECT operand of a set operator but cannot be used to group a set operator result. The set operators cannot be used in materialized view or a joined table.

Description

• When you use a correlation name, the correlation name must conform to the syntax rules for a basic name. All correlation names within one SELECT statement must be unique. Correlation names are useful when you join a table to itself. Define multiple correlation names for the table in the FROM clause and use the correlation names in the select list and the WHERE clause to qualify columns from that table. See "TableSpec" for more information about correlation names.

• SELECT...FOR UPDATE is supported in a SELECT statement that specifies a subquery, but it can be specified only in the outermost query.

• If your query specifies either FIRST NumRows or ROWS m TO n, ROWNUM may not be used to restrict the number of rows returned.

• FIRST NumRows and ROWS m TO n cannot be used together in the same SELECT statement.

• Use the SELECT...INTO statement in PL/SQL. If you use the SELECT...INTO statement outside of PL/SQL, TimesTen accepts, but silently ignores, the syntax.

Examples

This example shows the use of a column alias (max_salary) in the SELECT statement:

SELECT MAX(salary) AS max_salary 
FROM employees 
WHERE employees.hire_date > '2000-01-01 00:00:00';
< 10500 >
1 row found.

This example uses two tables, orders and lineitems.

The orders table and lineitems table are created as follows:

CREATE TABLE orders(orderno INTEGER, orderdate DATE, customer CHAR(20));

CREATE TABLE lineitems(orderno INTEGER, lineno INTEGER, 
  qty INTEGER, unitprice DECIMAL(10,2));

Thus for each order, there is one record in the orders table and a record for each line of the order in lineitems.

To find the total value of all orders entered since the beginning of the year, use the HAVING clause to select only those orders that were entered on or after January 1, 2000:

SELECT o.orderno, customer, orderdate, SUM(qty * unitprice)
FROM orders o, lineitems l
WHERE o.orderno=l.orderno
GROUP BY o.orderno, customer, orderdate
HAVING orderdate >= DATE '2000-01-01';

Consider this query:

SELECT * FROM tablea, tableb
WHERE tablea.column1 = tableb.column1 AND tableb.column2 > 5
FOR UPDATE;

The query locks all rows in tablea where:

• The value of tablea.column1 equals at least one tableb.column1 value where tableb.column2 is greater than 5.

The query also locks all rows in tableb where:

• The value of tableb.column2 is greater than 5.

• The value of tableb.column1 equals at least one tablea.column1 value.

If no WHERE clause is specified, all rows in both tables are locked.

This example demonstrates the (+) join operator:

SELECT * FROM t1, t2
WHERE t1.x = t2.x(+);

The following query returns an error because an outer join condition cannot be connected by OR.

SELECT * FROM t1, t2, t3
WHERE t1.x = t2.x(+) OR t3.y = 5;

The following query is valid:

SELECT * FROM t1, t2, t3
WHERE t1.x = t2.x(+) AND (t3.y = 4 OR t3.y = 5);

A condition cannot use the IN operator to compare a column marked with (+). For example, the following query returns an error.

SELECT * FROM t1, t2, t3
WHERE t1.x = t2.x(+) AND t2.y(+) IN (4,5);

The following query is valid:

SELECT * FROM t1, t2, t3
WHERE t1.x = t2.x(+) AND t1.y IN (4,5);

The following query results in an inner join. The condition without the (+) operator is treated as an inner join condition.

SELECT * FROM t1, t2
WHERE t1.x = t2.x(+) AND t1.y = t2.y;

In the following query, the WHERE clause contains a condition that compares an inner table column of an outer join with a constant. The (+) operator is not specified and hence the condition is treated as an inner join condition.

SELECT * FROM t1, t2
WHERE t1.x = t2.x(+) AND t2.y = 3;

For more join examples, see "JoinedTable".

The following example returns the current sequence value in the student table.

SELECT SEQ.CURRVAL FROM student;

The following query produces a derived table because it contains a SELECT statement in the FROM clause.

SELECT * FROM t1, (SELECT MAX(x2) maxx2 FROM t2) tab2 
WHERE t1.x1 = tab2.maxx2;

The following query joins the results of two SELECT statements.

SELECT * FROM t1 
WHERE x1 IN (SELECT x2 FROM t2) 
UNION 
SELECT * FROM t1 
WHERE x1 IN (SELECT x3 FROM t3);

In the following, select all orders that have the same price as the highest price in their category.

SELECT * FROM orders WHERE price = (SELECT MAX(price) 
FROM stock WHERE stock.cat=orders.cat);

The next example illustrates the use of the INTERSECT set operator. There is a department_id value in the employees table that is NULL. In the departments table, the department_id is defined as a NOT NULL primary key. The rows returned from using the INTERSECT set operator do not include the row in the departments table whose department_id value is NULL.

Command> SELECT department_id FROM employees INTERSECT SELECT department_id 
         FROM departments;
< 10 >
< 20 >
< 30 >
< 40 >
< 50 >
< 60 >
< 70 >
< 80 >
< 90 >
< 100 >
< 110 >
11 rows found.
Command> SELECT DISTINCT department_id FROM employees;
< 10 >
< 20 >
< 30 >
< 40 >
< 50 >
< 60 >
< 70 >
< 80 >
< 90 >
< 100 >
< 110 >
< <NULL> >
12 rows found.

The next example illustrates the use of the MINUS set operator by combining rows returned by the first query but not the second. The row containing the NULL department_id value in the employees table is the only row returned.

Command> SELECT department_id FROM employees 
         MINUS SELECT department_id FROM departments;
< <NULL> >
1 row found.

The following example illustrates the use of the SUBSTR expression in a GROUP BY clause and the use of a subquery in a HAVING clause. The first 10 rows are returned.

Command> SELECT ROWS 1 TO 10 SUBSTR (job_id, 4,10), department_id, manager_id, 
         SUM (salary) FROM employees
         GROUP BY SUBSTR (job_id,4,10),department_id, manager_id
         HAVING (department_id, manager_id) IN
          (SELECT department_id, manager_id FROM employees x
           WHERE x.department_id = employees.department_id)
         ORDER BY SUBSTR (job_id, 4,10),department_id,manager_id;
< ACCOUNT, 100, 108, 39600 >
< ACCOUNT, 110, 205, 8300 >
< ASST, 10, 101, 4400 >
< CLERK, 30, 114, 13900 >
< CLERK, 50, 120, 22100 >
< CLERK, 50, 121, 25400 >
< CLERK, 50, 122, 23600 >
< CLERK, 50, 123, 25900 >
< CLERK, 50, 124, 23000 >
< MAN, 20, 100, 13000 >
10 rows found.

The following example locks the employees table for update and waits 10 seconds for the lock to be available. An error is returned if the lock is not acquired in 10 seconds. The first five rows are selected.

Command> SELECT FIRST 5 last_name FROM employees FOR UPDATE WAIT 10;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
5 rows found.

The next example locks the departments table for update. If the selected rows are locked by another process, an error is returned if the lock is not available. This is because NOWAIT is specified.

Command> SELECT FIRST 5 last_name e FROM employees e, departments d 
         WHERE e.department_id = d.department_id 
         FOR UPDATE OF d.department_id NOWAIT;
< Whalen >
< Hartstein >
< Fay >
< Raphaely >
< Khoo >
5 rows found.

In the following, use the HR schema to illustrate the use of a subquery with the FOR UPDATE clause.

Command> SELECT employee_id, job_id FROM job_history 
         WHERE (employee_id, job_id) NOT IN (SELECT employee_id, job_id 
         FROM employees) 
         FOR UPDATE;
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

In the following, use a dynamic parameter placeholder for SELECT ROWS m TO n and SELECT FIRST.

Command> SELECT ROWS ? TO ? employee_id FROM employees;

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 (TT_INTEGER) > 1
Enter Parameter 2 (TT_INTEGER) > 3
< 100 >
< 101 >
< 102 >
3 rows found.
Command> SELECT ROWS :a TO :b employee_id FROM employees;

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 (TT_INTEGER) > 1
Enter Parameter 2 (TT_INTEGER) > 3
< 100 >
< 101 >
< 102 >
3 rows found.
Command> SELECT FIRST ? employee_id FROM employees;

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 (TT_INTEGER) > 3
< 100 >
< 101 >
< 102 >
3 rows found.

The following example illustrates the use of NULLS LAST in the ORDER BY clause. Query the employees table to find employees with a commission percentage greater than .30 or a commission percentage that is NULL. Select the first seven employees and order by commission_pct and last_name. Order commision_pct in descending order and use NULLS LAST to display rows with NULL values last in the query. Output commission_pct and last_name.

Command> SELECT FIRST 7 commission_pct,last_name
         FROM employees where commission_pct > .30
         OR commission_pct IS NULL
         ORDER BY commission_pct DESC NULLS LAST,last_name;
< .4, Russell >
< .35, King >
< .35, McEwen >
< .35, Sully >
< <NULL>, Atkinson >
< <NULL>, Austin >
< <NULL>, Baer >
7 rows found.

WithClause

Syntax

WithClause has the following syntax:

WITH QueryName AS ( Subquery ) [, QueryName AS ( Subquery )] ...

Parameters

WithClause has the following parameter:

Parameter	Description
QueryName AS (Subquery)	Specifies an alias for a subquery that can be used multiple times within the SELECT statement.

Description

Subquery factoring provides the WITH clause that enables you to assign a name to a subquery block, which can subsequently be referenced multiple times within the main SELECT query. The query name is visible to the main query and any subquery contained in the main query.

The WITH clause can only be defined as a prefix to the main SELECT statement.

Subquery factoring is useful in simplifying complex queries that use duplicate or complex subquery blocks in one or more places. In addition, TimesTen uses subquery factoring to optimize the query by evaluating and materializing the subquery block once and providing the result for each reference in the SELECT statement.

You can specify the set operators: UNION, MINUS, INTERSECT in the main query.

Restrictions using the WITH clause:

• Do not use the WITH clause in a view or materialized view definition.

• Recursive subquery factoring is not supported.

• Do not use the WITH clause in subqueries or derived tables.

• You cannot provide a column parameter list for the query alias. For example, TimesTen does not support: WITH w1(c1,c2) AS ...

Example

The following example creates the query names dept_costs and avg_cost for the initial query block, then uses these names in the body of the main query.

Command> WITH dept_costs AS (
         SELECT department_name, SUM(salary) dept_total
         FROM employees e, departments d
         WHERE e.department_id = d.department_id
         GROUP BY department_name),
          avg_cost AS (
         SELECT SUM(dept_total)/COUNT(*) avg
         FROM dept_costs)
         SELECT * FROM dept_costs
         WHERE dept_total >
         (SELECT avg FROM avg_cost)
         ORDER BY department_name;

> DEPARTMENT_NAME DEPT_TOTAL
-------------------------------
Sales 304500
Shipping 156400

SelectList

SQL syntax

The SelectList parameter of the SELECT statement has the following syntax:

{* | [Owner.]TableName.* | 
  { Expression | [[Owner.]TableName.]ColumnName |
    [[Owner.]TableName.]ROWID | NULL
  }
  [[AS] ColumnAlias] } [,...]

Parameters

The SelectList parameter of the SELECT statement has the following parameters:

Parameter	Description
*	Includes, as columns of the query result, all columns of all tables specified in the FROM clause.
[Owner.]TableName.*	Includes all columns of the specified table in the result.
Expression	An aggregate query includes a GROUP BY clause or an aggregate function. When the select list is not an aggregate query, the column reference must reference a table in the FROM clause. A column reference in the select list of an aggregate query must reference a column list in the GROUP BY clause. If there is no GROUP BY clause, then the column reference must reference a table in the FROM clause. See "GROUP BY Clause" for more information on the GROUP BY clause.
[[Owner.]Table.] ColumnName	Includes a particular column from the named owner's indicated table. You can also specify the CURRVAL or NEXTVAL column of a sequence. See "Using CURRVAL and NEXTVAL in TimesTen Classic" for more details.
[[Owner.]Table.] ROWID	Includes the ROWID pseudocolumn from the named owner's indicated table.
NULL	When NULL is specified, the default for the resulting data type is VARCHAR(0). You can use the CAST function to convert the result to a different data type. NULL can be specified in the ORDER BY clause.
ColumnAlias	Used in an ORDER BY clause, the column alias must correspond to a column in the select list. The same alias can identify multiple columns. {*|[Owner.]TableName.*| {Expression |[[Owner.]TableName.]ColumnName | [[Owner.]TableName.]ROWID } [[AS] ColumnAlias]} [,...]

Description

• The clauses must be specified in the order given in the syntax.

• TimesTen does not support subqueries in the select list.

• A result column in the select list can be derived in any of the following ways.

  • A result column can be taken directly from one of the tables listed in the FROM clause.

  • Values in a result column can be computed, using an arithmetic expression, from values in a specified column of a table listed in the FROM clause.

  • Values in several columns of a single table can be combined in an arithmetic expression to produce the result column values.

  • Aggregate functions (AVG, MAX, MIN, SUM, and COUNT) can be used to compute result column values over groups of rows. Aggregate functions can be used alone or in an expression. You can specify aggregate functions containing the DISTINCT qualifier that operate on different columns in the same table. If the GROUP BY clause is not specified, the function is applied over all rows that satisfy the query. If the GROUP BY clause is specified, the function is applied once for each group defined by the GROUP BY clause. When you use aggregate functions with the GROUP BY clause, the select list can contain aggregate functions, arithmetic expressions, and columns in the GROUP BY clause. See "GROUP BY Clause" for details on the GROUP BY clause.

  • A result column containing a fixed value can be created by specifying a constant or an expression involving only constants.

• In addition to specifying how the result columns are derived, the select list also controls their relative position from left to right in the query result. The first result column specified by the select list becomes the leftmost column in the query result, and so on.

• Result columns in the select list are numbered from left to right. The leftmost column is number 1. Result columns can be referred to by column number in the ORDER BY clause. This is especially useful to refer to a column defined by an arithmetic expression or an aggregate.

• To join a table with itself, define multiple correlation names for the table in the FROM clause and use the correlation names in the select list and the WHERE clause to qualify columns from that table.

• When you use the GROUP BY clause, one answer is returned per group in accordance with the select list, as follows:

  • The WHERE clause eliminates rows before groups are formed.

  • The GROUP BY clause groups the resulting rows. See "GROUP BY Clause" for more details.

  • The select list aggregate functions are computed for each group.

Examples

In the following example, one value, the average number of days you wait for a part, is returned:

SELECT AVG(deliverydays)
FROM purchasing.supplyprice;

The part number and delivery time for all parts that take fewer than 20 days to deliver are returned by the following statement.

SELECT partnumber, deliverydays
FROM purchasing.supplyprice
WHERE deliverydays < 20;

Multiple rows may be returned for a single part.

The part number and average price of each part are returned by the following statement.

SELECT partnumber, AVG(unitprice)
FROM purchasing.supplyprice
GROUP BY partnumber;

In the following example, the join returns names and locations of California suppliers. Rows are returned in ascending order by partnumber values. Rows containing duplicate part numbers are returned in ascending order by vendorname values. The FROM clause defines two correlation names (v and s), which are used in both the select list and the WHERE clause. The vendornumber column is the only common column between vendors and supplyprice.

SELECT partnumber, vendorname, s.vendornumber,vendorcity
  FROM purchasing.supplyprice s, purchasing.vendors v
  WHERE s.vendornumber = v.vendornumber AND vendorstate = 'CA'
ORDER BY partnumber, vendorname;

The following query joins table purchasing.parts to itself to determine which parts have the same sales price as the part whose serial number is '1133-P-01'.

SELECT q.partnumber, q.salesprice
  FROM purchasing.parts p, purchasing.parts q
  WHERE p.salesprice = q.salesprice AND p.serialnumber = '1133-P-01';

The next example shows how to retrieve the rowid of a specific row. The retrieved rowid value can be used later for another SELECT, DELETE, or UPDATE statement.

SELECT rowid
FROM purchasing.vendors
WHERE vendornumber = 123;

The following example shows how to use a column alias to retrieve data from the table employees.

SELECT MAX(salary) AS max_salary FROM employees;

TableSpec

SQL syntax

The TableSpec parameter of the SELECT statement has the following syntax:

TableNameSyntax | JoinedTable | DerivedTable

TableNameSyntax::=  [Owner.]TableName  [CorrelationName] |
                   ([Owner.]TableName) [CorrelationName] |
                   ([Owner.]TableName  [CorrelationName])

A simple table specification has the following syntax:

[Owner.]TableName or ([Owner.]TableName)

Parameters

The TableSpec parameter of the SELECT statement has the following parameters:

Parameter	Description
TableNameSyntax	Identifies a table to be referenced. Parentheses are optional.
CorrelationName	CorrelationName specifies an alias for the immediately preceding table. When accessing columns of that table elsewhere in the SELECT statement, use the correlation name instead of the actual table name within the statement. The scope of the correlation name is the SQL statement in which it is used. The correlation name must conform to the syntax rules for a basic name. See "Basic Names" for more information. All correlation names within one statement must be unique.
JoinedTable	Specifies the query that defines the table join. See "JoinedTable" for more information.
DerivedTable	Specifies a table derived from the evaluation of a SELECT statement. No FIRST NumRows or ROWS m TO n clauses are allowed in this SELECT statement. See "DerivedTable" for more information.

JoinedTable

The JoinedTable parameter specifies a table derived from CROSS JOIN, INNER JOIN, LEFT OUTER JOIN or RIGHT OUTER JOIN.

SQL syntax

The syntax for JoinedTable is as follows:

{CrossJoin | QualifiedJoin}

Where CrossJoin is:

TableSpec1 CROSS JOIN TableSpec2

And QualifiedJoin is:

TableSpec1 [JoinType] JOIN TableSpec2 ON SearchCondition

In the QualifiedJoin parameter, JoinType syntax is as follows:

{INNER | LEFT [OUTER] | RIGHT [OUTER]}

Parameters

The JoinedTable parameter of the TableSpec clause of a SELECT statement has the following parameters:

Parameter	Description
CrossJoin	Performs a cross join on two tables. A cross join returns a result table that is the cartesian product of the input tables. The result is the same as that of a query with the following syntax: SELECT Selectlist FROM Table1, Table2
QualifiedJoin	Specifies that the join is of type JoinType.
TableSpec1	Specifies the first table of the JOIN clause.
TableSpec2	Specifies the second table of the JOIN clause.
JoinType JOIN	Specifies the type of join to perform. These are the supported join types: INNER LEFT [OUTER] RIGHT [OUTER] INNER JOIN returns a result table that combines the rows from two tables that meet SearchCondition. LEFT OUTER JOIN returns join rows that match SearchCondition and rows from the first table that do not have SearchCondition evaluated as true with any row from the second table. RIGHT OUTER JOIN returns join rows that match SearchCondition and rows from the second table that do not have SearchCondition evaluated as true with any row from the first table.
ON SearchCondition	Specifies the search criteria to be used in a JOIN parameter. SearchCondition can refer only to tables referenced in the current qualified join.

Description

• FULL OUTER JOIN is not supported.

• A joined table can be used to replace a table in a FROM clause anywhere except in a statement that defines a materialized view. Thus, a joined table can be used in UNION, MINUS, INTERSECT, a subquery, a nonmaterialized view, or a derived table.

• A subquery cannot be specified in the operand of a joined table. For example, the following statement is not supported:

  SELECT * FROM
    regions INNER JOIN (SELECT * FROM countries) table2
    ON regions.region_id=table2.region_id;
  

• A view can be specified as an operand of a joined table.

• A temporary table cannot be specified as an operand of a joined table.

• OUTER JOIN can be specified in two ways, either using the (+) operator in SearchCondition of the WHERE clause or using a JOIN table operation. The two specification methods cannot coexist in the same statement.

• Join order and grouping can be specified with a JoinedTable operation, but they cannot be specified with the (+) operator. For example, the following operation cannot be specified with the (+) operator:

  t LEFT JOIN (t2 INNER JOIN t3 ON x2=x3) ON (x1 = x2 - x3)

Examples

These examples use the regions and countries tables from the HR schema.

The following performs a left outer join.

SELECT * FROM regions LEFT JOIN countries
  ON regions.region_id=countries.region_id
  WHERE regions.region_id=3;

< 3, Asia, JP, Japan, 3 >
< 3, Asia, CN, China, 3 >
< 3, Asia, IN, India, 3 >
< 3, Asia, AU, Australia, 3 >
< 3, Asia, SG, Singapore, 3 >
< 3, Asia, HK, HongKong, 3 >
6 rows found.

You can also perform a left outer join with the (+) operator, as follows.

SELECT * FROM regions, countries
  WHERE regions.region_id=countries.region_id (+)
    AND regions.region_id=3;

The following performs a right outer join.

SELECT * FROM regions RIGHT JOIN countries
  ON regions.region_id=wountries.region_id
  WHERE regions.region_id=3;

< AU, Australia, 3, 3, Asia >
< CN, China, 3, 3, Asia >
< HK, HongKong, 3, 3, Asia >
< IN, India, 3, 3, Asia >
< JP, Japan, 3, 3, Asia >
< SG, Singapore, 3, 3, Asia >
6 rows found.

The next example performs a right outer join with the (+) operator.

SELECT * FROM countries, regions
       WHERE regions.region_id (+)=countries.region_id
       AND countries.region_id=3;
< JP, Japan, 3, 3, Asia >
< CN, China, 3, 3, Asia >
< IN, India, 3, 3, Asia >
< AU, Australia, 3, 3, Asia >
< SG, Singapore, 3, 3, Asia >
< HK, HongKong, 3, 3, Asia >
6 rows found.

Note that the right join methods produce the same rows but in a different display order. There should be no expectation of row order for join results.

The following performs an inner join.

SELECT * FROM regions INNER JOIN countries
  ON regions.region_id=countries.region_id
  WHERE regions.region_id=2;

< 2, Americas, US, United States of America, 2 >
< 2, Americas, CA, Canada, 2 >
< 2, Americas, BR, Brazil, 2 >
< 2, Americas, MX, Mexico, 2 >
< 2, Americas, AR, Argentina, 2 >
5 rows found.

The next example performs a cross join.

SELECT * FROM regions CROSS JOIN countries 
  WHERE regions.region_id=1;

< 1, Europe, AR, Argentina, 2 >
< 1, Europe, AU, Australia, 3 >
< 1, Europe, BE, Belgium, 1 >
< 1, Europe, BR, Brazil, 2 >
...
< 1, Europe, SG, Singapore, 3 >
< 1, Europe, UK, United Kingdom, 1 >
< 1, Europe, US, United States of America, 2 >
< 1, Europe, ZM, Zambia, 4 >
< 1, Europe, ZW, Zimbabwe, 4 >
25 rows found.

See also

• CREATE TABLE
• INSERT
• INSERT...SELECT
• UPDATE

DerivedTable

A derived table is the result of a SELECT statement in the FROM clause, with an alias.

SQL syntax

The syntax for DerivedTable is as follows:

(Subquery) [CorrelationName]

Parameters

The DerivedTable parameter of the TableSpec clause of a SELECT statement has the following parameters:

Parameter	Description
Subquery	See "Subqueries" for information on subqueries.
CorrelationName	Optionally use CorrelationName to specify an alias for the derived table. It must be different from any table name referenced in the query.

Description

When using a derived table, these restrictions apply:

• The DUAL table can be used in a SELECT statement that references no other tables, but needs to return at least one row. Selecting from DUAL is useful for computing a constant expression (an expression that is evaluated to a constant value) with the SELECT statement. Because DUAL has only one row, the constant is returned only once.

• Subquery cannot refer to a column from another derived table.

• A derived table cannot be used as a source of a joined table.

• A derived table cannot be used as a target of a DELETE or UPDATE statement.