Can you explain what a nested query in SQL is and when it is used?

A subquery in SQL is a query that is nested inside another query. It is typically used to retrieve data that is then used in the outer query. For example, you can first select a set of values and then use them in the main query.

Example: find all employees whose salary is above the average for the company.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Here, the subquery (SELECT AVG(salary) FROM employees) calculates the average salary, and the outer query selects employees with a salary above this value.

Nested queries are used for filtering, comparison, existence checks, and other operations where the result of one query is needed for another.