# 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)

#### 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 *
    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)

Parent topic: Statements
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
GRANT statement
REVOKE 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