Subqueries, EXISTS, ANY & ALL

A subquery is a SELECT inside another statement, in parentheses:

SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products);

Kinds of subquery: • scalar — returns one value (as above), • list — returns one column, used with IN, • table — returns rows and columns, used in FROM (a derived table, which needs an alias).