-- avg always returns a single value, so the subquery is
-- a ScalarSubquery
SELECT NAME, COMM
FROM STAFF
WHERE EXISTS
(SELECT AVG(BONUS + 800)
FROM EMPLOYEE
WHERE COMM < 5000
AND EMPLOYEE.LASTNAME = UPPER(STAFF.NAME)
)
-- Introduce a way of "generating" new data values,
-- using a query which selects from a VALUES clause (which is an
-- alternate form of a fullselect).
-- This query shows how a table can be derived called "X" having
-- 2 columns "R1" and "R2" and 1 row of data.
SELECT R1,R2
FROM (VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)

TableSubquery

A TableSubquery is a subquery that returns multiple rows.

Unlike a

ScalarSubquery

, a TableSubquery is allowed only:

· as a

TableExpression

in a

FROM clause

· with EXISTS, IN, or quantified comparisons.

When used as a

TableExpression

in a

FROM clause

, it can return multiple columns.

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.

Syntax

(

Query

[

ORDER BY clause

]

[

result offset clause

]

[

fetch first clause

]

)

Example

-- a subquery used as a TableExpression in a FROM clause
SELECT VirtualFlightTable.flight_ID
FROM
(SELECT flight_ID, orig_airport, dest_airport
FROM Flights
WHERE (orig_airport = 'SFO' OR dest_airport = 'SCL') )
AS VirtualFlightTable
-- a subquery (values expression) used as a TableExpression
-- in a FROM clause
SELECT mycol1
FROM
(VALUES (1, 2), (3, 4))
AS mytable (mycol1, mycol2)
-- a subquery used with EXISTS
SELECT *