A subquery is a SELECT statement enclosed in parentheses. Such a SELECT statement must contain one and only one select list item.
A column can be compared to a subquery in a comparison condition (for example, >,<, or !=) as long as the subquery returns no more than one row. If the subquery (which must have one column) returns one row, the value of that row is compared to the expression. If a subquery returns no rows, its value is NULL.
Subqueries that return exactly one column and any number of rows can be used in IN conditions, ANY conditions, ALL conditions, or EXISTS conditions. These conditions are discussed in the following sections.
SAP Sybase IQ supports UNION only in uncorrelated subquery predicates, not in scalar value subqueries or correlated subquery predicates.
Subqueries cannot be used inside a CONTAINS or LIKE predicate.
CREATE VARIABLE @ln int;SELECT @ln = 1;select count(*) FROM lineitemWHERE l_shipdate IN (select l_shipdate FROM lineitem WHERE l_orderkey IN (2,4,6))OR l_shipdate IN (select l_shipdate FROM lineitem WHERE l_orderkey IN (1,3,5))OR l_linenumber = @ln;
Similar subqueries joined by AND and BETWEEN are allowed.