Query

Create a virtual table based on existing tables or constants built into tables.

Syntax

{ 
( Query ) 
| 
SelectExpression  | VALUES Expression 
} 
Note: Using Union, Intersect, or Except is not supported in this release of GemFire XD, even though GemFire XD does not throw a syntax error. See SQL Language Limitations.

Description

A query creates a virtual table based on existing tables or constants built into tables.

Examples

-- a Select expression 
SELECT * FROM TRADE.CUSTOMERS 
-- a subquery 
SELECT * FROM TRADE.CUSTOMERS C WHERE EXISTS (SELECT * FROM TRADE.PORTFOLIO F WHERE F.CID = C.CID AND TID =? AND F.QTY >927)
-- a values expression 
VALUES (1,2,3

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