TableSubquery

A TableSubquery is a subquery that returns multiple rows.

Unlike a ScalarSubquery, a TableSubquery is allowed only:

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)
-- 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 *
FROM Flights
WHERE EXISTS
    (SELECT * FROM Flights WHERE dest_airport = 'SFO'
    AND orig_airport = 'GRU')
-- a subquery used with IN
SELECT flight_id, segment_number
FROM Flights
WHERE flight_id IN
    (SELECT flight_ID
    FROM Flights WHERE orig_airport = 'SFO'
    OR dest_airport = 'SCL')
-- a subquery used with a quantified comparison
SELECT NAME, COMM 
FROM STAFF 
WHERE COMM > 
(SELECT AVG(BONUS + 800)
     FROM EMPLOYEE
     WHERE COMM < 5000);
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause