What is a subquery in SQL?

A subquery (or inner query / nested query) is a query embedded inside another SQL query. The inner query executes first and its result is used by the outer query. Types by location: (1) In WHERE: SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products); — products priced above average; (2) In FROM (derived table): SELECT dept, avg_sal FROM (SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department) AS dept_stats WHERE avg_sal > 50000;; (3) In SELECT (scalar subquery): SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count FROM users;. Types by return value: Scalar — returns one value; Row — returns one row; Table — returns multiple rows/columns. Correlated subquery: references a column from the outer query — re-executes for each outer row (can be slow). Operators with subqueries: IN, NOT IN, EXISTS, NOT EXISTS, ANY, ALL. Often JOINs can replace subqueries with better performance.