# 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, or with EXISTS, it can return 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