# 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 *
    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 with ORDER BY and FETCH FIRST clauses
    SELECT flight_id, segment_number
    FROM Flights
    WHERE flight_id IN
        (SELECT flight_ID
        FROM Flights WHERE orig_airport = 'SFO'
        OR dest_airport = 'SCL' ORDER BY flight_id FETCH FIRST 12 ROWS ONLY)
    -- 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: SQL queries