Subqueries
TimesTen supports subqueries in INSERT...SELECT
, CREATE VIEW
or UPDATE
statements and in the SET
clause of an UPDATE
statement, in a search condition and as a derived table. TimesTen supports table subqueries and scalar subqueries. TimesTen 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
)
Where ValueList
is a list of constant expressions. Each constant expression specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values (?
or :DynamicParameter
), NULL
, and calls to functions that return constant values.
Description
Table subquery:
-
A subquery can appear in the
WHERE
clause orHAVING
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 aWHERE
orHAVING
clause, anOR
expression within aWHERE
orHAVING
clause, or anON
clause of a joined table. They cannot be specified in aCASE
expression, a materialized view, or aHAVING
clause that uses the+
operator for outer joins. -
A subquery can be specified in an
EXISTS
orNOT EXISTS
predicate, a quantified predicate withANY
orALL
, or a comparison predicate. The allowed operators for both comparison and quantified predicates are:=
,<
,>
,<=
,>=
,<>
. The subquery cannot be connected to the outer query through aUNIQUE
orNOT 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 isNULL
. It is an error if the subquery returns multiple rows.
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.
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);