SQL Subquery

In SQL, a subquery is a query within another query that returns data used in the outer query. A subquery can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE, and HAVING clauses.

A subquery can be used to simplify complex queries by breaking them down into smaller, more manageable pieces. Subqueries can also be used to answer questions that involve multiple tables, or to perform calculations or comparisons that would be difficult to achieve in a single query.

Here's an example of how to use a subquery in SQL:

Suppose you have a table named "employees" with the following data:

1+----+---------+---------+---------+
2| ID | Name    | Salary  | Manager |
3+----+---------+---------+---------+
4|  1 | Max     | 50000   |   NULL  |
5|  2 | Jane    | 55000   |    1    |
6|  3 | Mike    | 60000   |    1    |
7|  4 | Alice   | 65000   |    1    |
8|  5 | Rachel  | 70000   |    2    |
9+----+--------+----------+---------+

Suppose you want to retrieve the names of the employees who are managed by the employee with the ID 1 (Max). The following SQL statement uses a subquery to accomplish this task:

1SELECT Name
2FROM employees
3WHERE Manager = (SELECT ID
4                 FROM employees
5                 WHERE Name = 'Max');

The subquery in this example is:

1SELECT ID
2FROM employees
3WHERE Name = 'Max';

This subquery retrieves the ID of the employee named Max. The result of this subquery is then used in the outer query to retrieve the names of the employees who are managed by Max. The outer query would return the following result:

1+--------+
2| Name   |
3+--------+
4| Jane   |
5| Bob    |
6| Alice  |
7+--------+