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