Subqueries

TimesTen supports subqueries in INSERT...SELECT, CREATE VIEW, CREATE VIEW or UPDATE statements or in an update SET clause, in a search condition and as a derived table. TimesTen supports table subqueries and scalar subqueries. It does not support row subqueries. A subquery can specify an aggregate with a HAVING clause or joined table. It can also be correlated.

SQL syntax

[NOT] EXISTS | [NOT] IN (Subquery)
Expression {= | <> | > | >= | < | <= } [ANY | ALL] (Subquery)
Expression [NOT] IN (ValueList | Subquery)

Description

TimesTen supports queries with the characteristics listed in each section.

Table subqueries

• A subquery can appear in the WHERE clause or HAVING clause of any statement, except one that creates a MATERIALIZED VIEW. Only one table subquery can be specified in a predicate. These predicates can be specified in a WHERE or HAVING clause, an OR expression within a WHERE or HAVING clause, or an ON clause of a joined table. They cannot be specified in a CASE expression, a materialized view, or a HAVING clause that uses the + operator for outer joins.

• A subquery can be specified in an EXISTS or NOT EXISTS predicate, a quantified predicate with ANY or ALL, or a comparison predicate. The allowed operators for both comparison and quantified predicates are: =, <, >, <=, >=, <>. The subquery cannot be connected to the outer query through a UNIQUE or NOT UNIQUE operator.

• Only one subquery can be specified in a quantified or comparison predicate. Specify the subquery as either the right operand or the left operand of the predicate, but not both.

• The subquery should not have an ORDER BY clause.

• FIRST NumRows is not supported in subquery statements.

• In a query specified in a quantified or comparison predicate, the underlying SELECT must have a single expression in the select list. In a query specified in a comparison predicate, if the underlying select returns a single row, the return value is the select result. If the underlying select returns no row, the return value is NULL. It is an error if the subquery returns multiple rows.

Scalar subqueries (a scalar subquery returns a single value)

• A nonverifiable scalar subquery has a predicate such that the optimizer cannot detect at compile time that the subquery returns at most one row for each row of the outer query. The subquery cannot be specified in an OR expression.

• Neither outer query nor any scalar subquery should have a DISTINCT modifier.

Examples

Examples of supported subqueries for a list of customers having at least one unshipped order:

SELECT customers.name FROM customers
  WHERE EXISTS (SELECT 1 FROM orders
    WHERE customers.id = orders.custid
    AND orders.status = 'unshipped');

SELECT customers.name FROM customers
    WHERE customers.id = ANY 
        (SELECT orders.custid FROM orders
WHERE orders.status = 'unshipped');

SELECT customers.name FROM customers 
    WHERE customers.id IN 
        (SELECT orders.custid FROM orders 
WHERE orders.status = 'unshipped');

In this example, list items are shipped on the same date as when they are ordered:

SELECT line_items.id FROM line_items 
    WHERE line_items.ship_date = 
        (SELECT orders.order_date FROM orders 
WHERE orders.id = line_items.order_id);