A subquery is an expression that consists a query. The subquery is correlated with external queries when it references columns beyond the subquery. Presto has limited support for correlated subqueries.
EXISTS predicate determines whether a subquery returns all rows. If the subquery returns rows, the WHERE expression is TRUE. If no rows are returned, the WHERE expression is FALSE.
SELECT name FROM nation WHERE EXISTS (SELECT * FROM region WHERE region.regionkey = nation.regionkey);
The IN predicate determines whether any columns specified by
WHERE are included in the result set produced by the subquery. If yes, results are returned. If not, no results are returned. A subquery returns only one column.
SELECT name FROM nation WHERE regionkey IN (SELECT regionkey FROM region);
A scalar subquery is a non-correlated subquery that returns zero or one row. The subquery cannot return more than one row. The returned value is
NULL if the subquery returns no rows.
SELECT name FROM nation WHERE regionkey = (SELECT max(regionkey) FROM region)