TurboSQL Guide

Sub-Queries

Previous  Top  Next

Search-conditions within SELECT, INSERT and UPDATE statements may contain embedded queries, which can be compared to the main query via one of the following operators. Furthermore, a select expression in parenthesis can be used everywhere an expression is expected. TurboSQL allows for uncorrelated sub-selects as well as for correlated ones.

IN

Checks whether the value of an expression can be found in the result set of the sub-query.

Example

select * from SALESINFO
where customerName in (
select name from CUSTOMER where state = 'CA'
)

Selects all sales to customers from California and is basically the same as

select * from SALESINFO join CUSTOMER on customerName = name
where state = 'CA'

EXISTS

Checks whether the sub-query contains at least one row.

Example

select * from SALESINFO
where exists (
select * from CUSTOMER
where name = SALESINFO.customerName and state = 'CA'
)

Retrieves the same result as the first example. Note however that this time the sub-query contains a column reference to the outer query. This is called correlated sub-query.

ANY/SOME

Checks whether there is at least one row in the result of the sub-query, which satisfies the search-condition.

Example

select * from SALESINFO
where amount > any (
select averageAmount from CUSTOMER
where name = SALESINFO.customerName
)

Retrieves all sales bigger than the average for the respective customer.

ALL

Checks whether the search-condition is satisfied for all rows in the result of the sub-query.

Example

select * from SALESINFO
where amount > all (
select averageAmount from CUSTOMER
where state = 'CA'
)

Retrieves the sales bigger than the average volume for each single customer in California.

Sub-Query as Expression

A select expression in parenthesis can be used as a scalar expression. The type of the scalar expression is the type oft the first column in the result set. The value of the scalar expression is the value of the first column in the first row. If the result set has no column, the expression is invalid. If it has no rows, the result value is NULL.

Examples:

select * from [TableB] where C1 like (select C2 from TableB) || '%'

set A = (select Count(*) from TableA)

Compatibility Information

The use of a sub-select as an expression is only available in TurboDB Managed.

See also

WHERE