1.1

ScalarSubquery
You can place a ScalarSubquery anywhere an Expression is permitted. A ScalarSubquery turns a SelectExpression
result into a scalar value because it returns only a single row and column value.
Description
The query must evaluate to a single row with a single column.
Sometimes also called an expression subquery.
Example
-- avg always returns a single value, so the subquery is a
ScalarSubquery
SELECT S.CID FROM TRADE.SELLORDERS S WHERE (SELECT SUM(QTY)
FROM TRADE.PORTFOLIO F WHERE F.CID = S.CID GROUP BY F.CID) >
1277 AND TID =1
TableSubquery
A TableSubquery is a subquery that returns multiple rows.
Unlike a ScalarSubquery, a TableSubquery is allowed only with EXISTS, IN, or quantified comparisons.
When used with EXISTS, it returns multiple columns only if you use * to return the multiple columns.
When used with IN or quantified comparisons, it must return a single column.
Note: Using TableSubquery as a TableExpression in a FROM clause is not supported, even though this
does not throw a syntax error.
Examples
-- a subquery used with EXISTS
SELECT C.CID FROM TRADE.CUSTOMERS C WHERE EXISTS (SELECT *
FROM TRADE.PORTFOLIO F WHERE F.CID = C.CID AND TID = 1 AND
F.QTY >927)
-- a subquery used with IN
SELECT F.CID FROM TRADE.PORTFOLIO F WHERE F.CID IN (SELECT
CID FROM TRADE.CUSTOMERS C WHERE SINCE > ?)
-- a subquery used with a quantified comparison
SELECT S.CID FROM TRADE.SELLORDERS S WHERE (SELECT SUM(QTY)
FROM TRADE.PORTTOLIO F WHERE F.CID = S.CID GROUP BY F.CID) >
1231
SQL Clauses
For each SQL clause in this release of VMWare vFabric SQLFire, read descriptions of syntax, custom extensions,
and examples.
FOR UPDATE Clause
SQLFire does not support this clause from a thin client connection.
FROM Clause
Specify the tables from which the other clauses of the query can access columns for use in expressions.
vFabric SQLFire User's Guide518
vFabric SQLFire Reference