tableSubquery A tableSubquery is a subquery that returns multiple rows. tableSubquery

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)