Subqueries in the FROM Clause
You can specify a subquery in the FROM clause. This is known as a streamed subquery. The subquery is treated the same as a table, including its use in JOIN syntax and the optional assignment of an alias using the AS keyword. A FROM clause can contain multiple tables, views, and subqueries in any combination, subject to the restrictions of the JOIN syntax, as described in JOIN.
A subquery is enclosed in parentheses. The following example shows a subquery in a FROM clause:
SELECT name,region
FROM (SELECT t1.name,t1.state,t2.region
FROM Employees AS t1 LEFT OUTER JOIN Regions AS t2
ON t1.state=t2.state)
GROUP BY region
A subquery can specify a TOP clause. A subquery can contain an ORDER BY clause when paired with a TOP clause.
A subquery can use SELECT * syntax, subject to the following restriction: because a FROM clause results in a value expression, a subquery containing SELECT * must yield only one column.
A join within a subquery cannot be a NATURAL join or take a USING clause.
FROM Subqueries and %VID
When a FROM subquery is invoked, it returns a %VID for each subquery row returned. A %VID is an integer counter field; its values are system-assigned, unique, non-null, non-zero, and non-modifiable. The %VID is only returned when explicitly specified. It is returned as data type INTEGER. Because %VID values are sequential integers, they are far more meaningful if the subquery returns ordered data; a subquery can only use an ORDER BY clause when it is paired with a TOP clause.
Because the %VID is a sequential integer, it can be used to determine the ranking of items in a subquery with an ORDER BY clause. In the following example, the 10 newest records are listed in Name order, but their timestamp ranking is easily seen using the %VID values:
SELECT Name,%VID,TimeStamp FROM
(SELECT TOP 10 * FROM MyTable ORDER BY TimeStamp DESC)
ORDER BY Name
One common use of the %VID is to “window” the result set, dividing execution into sequential subsets that fit the number of lines available in a display window. For example, display 20 records, then wait for the user to press Enter, then display the next 20 records.
The following example uses %VID to “window” the results into subsets of 10 records:
SELECT %VID,* FROM
(SELECT TOP 60 Name, Age FROM Sample.Person WHERE Age > 55 ORDER BY Name)
WHERE %VID BETWEEN ? AND ?
For details on using %VID, refer to Defining and Using Views.
