Condition with Subquery

Include a SELECT statement within a condition specifies a condition with subquery. You can use a subquery in a SELECT, INSERT, DELETE, or UPDATE statement to perform tasks like the following:

Compare an expression to the result of the query.
Determine if an expression is included in the results of the query.
Ask whether the query selects any rows.

^{(explicit id csub001)
csub001} ^{(explicit id csub002)
csub002} ^{(explicit id csub003)
csub003}

Condition with Subquery

{ <EXISTS Subquery>^[] | <IN Subquery> ^[] | <ALL, ANY, SOME Subquery>^[] }

The subquery can depend on the current row that the outer SELECT statement is evaluating; in this case, the subquery is called a correlated subquery. (For a discussion of correlated subqueries and their impact on performance, see the Informix® Guide to SQL: Tutorial.)

The following sections describe subquery conditions and their syntax.

For a discussion of types of subquery conditions in the context of the SELECT statement, see Using a Condition in the WHERE Clause.
For a discussion of types of subquery conditions in the context of the INSERT statement, see Subset of SELECT Statement..
For a discussion of types of subquery conditions in the context of the DELETE statement, see Subqueries in the WHERE Clause of DELETE..
For a discussion of types of subquery conditions in the context of the UPDATE statement, see Subqueries in the WHERE Clause of UPDATE.

A subquery can return a single value, no value, or a set of values, depending on its context. If a subquery returns a value, it must select only a single column. If the subquery simply checks whether a row (or rows) exists, it can select any number of rows and columns.

A subquery cannot reference BYTE or TEXT columns, nor can it contain an ORDER BY clause. A subquery that specifies a table expression in the FROM clause, however, can include the ORDER BY clause.

A subquery and its outer DML statement operate on the same table object if the FROM clause of the subquery specifies the same table or view that the outer statement references in one of these clauses:

in the FROM clause of the DELETE or SELECT statement
in the INTO clause of the INSERT statement
in the Table Options or Collection Derived Table specification of the UPDATE statement.

Subqueries that return more than one row and that operate on the same table or view as the enclosing DML statement are valid only in the WHERE clause of the DELETE or UPDATE statement. Even in this context, such subqueries return error -360 unless all of the following conditions are satisfied:

The subquery does not reference any column name in its FROM list that is in a table not specified in the projection list
The subquery is specified using the Condition with Subquery syntax.
Any SPL routines within the subquery cannot reference the table that is being modified.

The following program fragment includes examples of conditions with subqueries in UPDATE and DELETE statements:

CREATE TABLE t1 ( a INT, a1 INT)
CREATE TABLE t2 ( b INT, b1 INT) ;
. . .
UPDATE t1 SET a = a + 10 WHERE EXISTS  
   (SELECT a FROM t1 WHERE a > 1);
UPDATE t1 SET a = a + 10 WHERE a IN
   (SELECT a FROM t1, t2  WHERE a > b 
	   AND a IN
         (SELECT a FROM t1 WHERE a > 50 ) );
      DELETE FROM t1 WHERE EXISTS 
   (SELECT a FROM t1);

For more information about subqueries in the DELETE statement, see Subqueries in the WHERE Clause of DELETE.

For more information about subqueries in the UPDATE statement, see Subqueries in the WHERE Clause of UPDATE .

¹ See EXISTS Subquery condition

² See IN Subquery

³ See ALL, ANY, and SOME Subqueries