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

SQL syntax

The general syntax for a SELECT statement is:

SELECT [FIRST NumRows | ROWS M TO N] [ALL | DISTINCT] SelectList
   FROM TableSpec [,...]
   [WHERE SearchCondition]
   [GROUP BY Expression [,...]]
   [HAVING SearchCondition]
   [ORDER BY {ColumnID|ColumnAlias|Expression} [ASC | DESC]]
   [,...]
   [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:

SELECT [ROWS m TO n] [ALL] SelectList
   FROM TableSpec [,...]
    [WHERE SearchCondition]
    [GROUP BY Expression [,...]]
    [HAVING SearchCondition] [,...]
  {UNION [ALL] | MINUS | INTERSECT}
  SELECT [ROWS M TO N] [ALL] SelectList
    FROM TableSpec [,...]
     [WHERE SearchCondition]
     [GROUP BY Expression [,...]]
     [HAVING SearchCondition] [,...]
     [ORDER BY {ColumnID|ColumnAlias|Expression} [ASC | DESC]

Parameters

The SELECT statement has the parameters:

Description

• When using a correlation name, the correlation name must conform to the syntax rules for a basic name. (See "Basic names".) All correlation names within one SELECT query 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 SelectList and the WHERE clause to qualify columns from that table.

• SELECT... FOR UPDATE is supported in a SELECT that specifies a subquery, but it can only be specified 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.

Examples

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

SELECT MAX(salary) AS max_salary 
FROM employee WHERE employee.age < 30;

This example assumes there are two tables, orders and lineitems.

The orders table is created as shown below:

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';

This query locks all rows in tablea where:

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

In addition, this query locks all rows in tableb where:

• tableb.column2 is greater than 5

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

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

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

This query returns an error, since the inner table t2 corresponds to two outer tables (t1 and t3):

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

This example demonstrates valid syntax:

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

This 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;

But 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);

But 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 instead of an outer join, because the (+) operator was not specified in each of the join conditions, and the condition without the (+) 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 was 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;

The following query returns an error because two tables cannot be outer joined together:

SELECT * FROM t1, t2
WHERE x1 = x2(+) AND y2 = y1(+);

Find the current sequence value in the student table.

SELECT SEQ.CURRVAL FROM student;

In the following query, the condition 'x2 + y2(+) = 1' is treated as an inner join condition because the (+) operator was not specified for the column x2 of inner table t2. The statement returns an error because two tables cannot be outer joined together:

SELECT * FROM t1, t2
WHERE x1 = x2(+) AND x2 + y2(+) = 1;

The following query does not specify an outer join because the (+) operator is not specified in a join condition:

SELECT * FROM t1, t2
WHERE x2(+) = 1;

The following query produces a derived table, as 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);

Select all orders that have the same price as the highest price in its category:

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

The example illustrates the use of the INTERSECT set operator. There is a department_id 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 does not include the row in the departments table whose department_id 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 example illustrates the use of the MINUS set operator by combining rows returned by first query but not the second. The row containing the NULL department_id 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 sums the salaries for employees in the employees table and uses the SUBSTR expression to group the data by job function.

Command> SELECT SUBSTR (job_id, 4,10), SUM (salary) FROM employees
GROUP BY SUBSTR (job_id,4,10);
< PRES, 24000 >
< VP, 34000 >
< PROG, 28800 >
< MGR, 24000 >
< ACCOUNT, 47900 >
< MAN, 121400 >
< CLERK, 133900 >
< REP, 273000 >
< ASST, 4400 >
9 rows found.

The 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 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 5 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 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.

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.

Illustrate use of 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 Eeployees;

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 Eeployees;

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.

---

SelectList

SQL syntax

The SelectList parameter of the SELECT statement has the syntax:

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

Parameters

The SelectList parameter of the SELECT statement has the parameters:

Description

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

• TimesTen does not support subqueries in the SelectList.

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

  • The 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 option 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.

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

• 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 if you want 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:

  • The WHERE clause eliminates rows before groups are formed.

  • The GROUP BY clause groups the resulting rows.

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

Examples

One value, the average number of days you wait for a part, is returned by the statement:

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 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 statement:

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

In this example, the join returns names and locations of California suppliers. Rows are returned in ascending partnumber order. Rows containing duplicate part numbers are returned in ascending vendorname order.

The FROM clause defines two correlation names (v and s), which are used in both the select list and the WHERE clause.

vendornumber 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;

This 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';

This 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;

This 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 syntax:

{[Owner.]TableName [CorrelationName] | JoinedTable | DerivedTable}

A simple table specification has the syntax:

[Owner.]TableName

Parameters

The TableSpec parameter of the SELECT statement has the parameters:

---

DerivedTable

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

SQL syntax

The syntax for DerivedTable is:

(Subquery) [CorrelationName]

Parameters

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

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 the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once.

• The SelectQuery 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 an UPDATE statement.

---

JoinedTable

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

SQL syntax

The syntax for JoinedTable is:

{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:

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

Parameters

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

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 to define a materialized view. Therefore, a joined table can be used in a UNION, MINUS or INTERSECT, a subquery, a non-materialized view or a derived table.

• A temporary table cannot be specified as an operand of a joined table, but a view can.

• OUTER JOIN can be specified in two ways, using the (+) operator in the SearchCondition of the WHERE clause, or to use a JOIN table operation. The two cannot co-exist in the same statement.

• Join order and grouping can be specified with a JoinedTable operation, but not with (+). For example, the following operation is not supported:

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

Examples

The following statement joins tables t1 and t2, returning all the rows from t1 where x1 is less than 10:

SELECT * FROM t1 LEFT JOIN t2 ON x1=x2 WHERE x1<10;

See also

CREATE TABLE
INSERT
INSERT...SELECT
UPDATE