Subquery

Subquery is a query nested inside another query. In SQL, the inner query is evaluated to supply values to the outer query. Subqueries can appear in the SELECT list, the FROM clause, or the WHERE and HAVING clauses, and they can return a single value, a single row, or a set of rows.

Scalar subquery: returns a single value; used in a WHERE condition or in a select. Example: SELECT name FROM employees WHERE salary = (SELECT AVG(salary) FROM employees);

Table subquery (set): returns multiple rows; commonly used with IN or EXISTS or as derived table in FROM. Example: SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status='open');

Correlated subquery: inner query references columns from the outer query; executed once per outer row; often less efficient but can express complex conditions. Example: SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM salaries s WHERE s.emp_id = e.id AND s.amount > 50000);

Non-correlated subquery: inner query does not reference outer query; can be evaluated once.

Derived table: subquery in FROM clause; acts as a temporary table. Example: SELECT t.product_id, t.total FROM (SELECT product_id, SUM(quantity) AS total FROM sales GROUP BY product_id) AS t WHERE t.total > 100;

Uses and limitations: Some databases require scalar subqueries to return exactly one row and one column; otherwise an error occurs. Some optimizations rewrite subqueries to joins; in other cases, lateral or apply syntax is used. Subqueries offer readability and expressiveness, but may impact performance compared to equivalent joins in some situations.