Learn how to use subqueries to perform complex, multi-step queries.
A nested query, or subquery, is a `SELECT` statement that is embedded inside another SQL statement. The outer statement is called the main query, and the inner query is the subquery. Subqueries are a powerful tool for breaking down complex problems into a series of logical steps. A subquery can be used in several places, most commonly in the `WHERE` clause, the `FROM` clause, or the `SELECT` list. When used in the `WHERE` clause, a subquery can return a single value (a scalar subquery), a list of values (a multi-row subquery, often used with `IN` or `NOT IN`), or it can be used with `EXISTS` to check for the existence of any rows. For example, to find all employees who earn more than the average salary, you could use a subquery: `SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);`. When a subquery is used in the `FROM` clause, it is often called a 'derived table' or an 'inline view'. It acts as a temporary table that the main query can then select from, which is useful for performing further aggregations on an already aggregated result. While many subqueries can be rewritten as joins (which is often more performant), they provide a clear and intuitive way to express complex logic, making them an indispensable part of an SQL developer's toolkit.