IBM PureData System for Analytics, Version 7.1

Subqueries

A subquery is a select expression that is enclosed in parentheses as a nested query block in a query statement.

You can use these nested query blocks in any of the following SQL statements:

SELECT
INSERT
DELETE
UPDATE
CREATE TABLE AS
INSERT INTO
SELECT INTO

You can nest subqueries to any arbitrary depth.

The parent query that contains the subquery is often called a super query or outer query. Subqueries in the same parent are used to derive sets of results that can be evaluated in combination with the parent query.

Subqueries can be further divided into the following categories:

Row subquery

Returns one row (or zero rows) and multiple columns, and can occur in a SELECT list or in a condition expression; for example, as an argument of a comparison operator.

Table subquery

Returns multiple rows (0 - n rows) and multiple columns, and can exist in a FROM clause or as an argument of an EXISTS, IN, ANY, or ALL test.

For example, “List all stores where sales are more than one percent of all company sales,” can be written in the SQL query-within-a-query form by using a scalar subquery:

   SELECT StoreId FROM Stores
   WHERE TotalSale > 0.01*
   (SELECT SUM(TotalSales) FROM Stores);

The system calculates the sum of sales of the inner subquery first and then uses it when it runs the outer query.

Singleton subquery

Returns exactly one value in the format of one row (or zero rows), one column table.

For all these subqueries, the system evaluates them once and calculates and stores their select expression. When you run the super query, the system substitutes the computed values in place of the subquery.

A correlated subquery, however, is a query within a query that references (or correlates) with the outer query. With correlated subqueries, the system evaluates the subquery repeatedly once for every row that is selected from the outer table.