Table Subquery

A TableSubquery is a subquery that returns multiple rows.

Syntax

Usage

Unlike a ScalarSubquery, a TableSubquery is allowed only:

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.

Example

This example shows a subquery used as a table expression 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;

This shows one subquery used with EXISTS and another used with IN:

SELECT *
  FROM Flights
  WHERE EXISTS
    (SELECT *
       FROM Flights
       WHERE dest_airport = 'SFO'
       AND orig_airport = 'GRU');

SELECT flight_id, segment_number
  FROM Flights
  WHERE flight_id IN
    (SELECT flight_ID
       FROM Flights
       WHERE orig_airport = 'SFO'
       OR dest_airport = 'SCL');

See Also