A subquery can contain one or more subqueries. Subqueries are used when the condition of a query is dependent on the result of another query, which in turn is dependent on the result of another subquery.
Consider an example. You need to view the DepartmentID of an employee whose e-mail address is Sales Agent. To perform this task, you can use the following query:
SELECT DepartmentID FROM HumanResources.EmployeeDepartmentHistory
WHERE BusinessEntityID = /* Level 1 inner query */
(SELECT BusinessEntityID FROM Person.BusinessEntityContact
WHERE ContactTypeID = /* Level 2 inner query */
(SELECT ContactTypeID FROM Person.ContactType WHERE Name= 'Sales Agent')
)
In the preceding example, two queries are nested within another query. The level 2 inner query returns the Contact ID of an employee based on the e-mail address of the employee from the Person table.
The level 1 inner query uses this ContactID to search for the BusinessEntityID of the employee with the given e-mail address. The main query uses the BusinessEntityID returned by level 1 inner query to search for the DepartmentID from the EmployeeDepartmentHistory table.
You can implement subqueries upto 32 levels. However, the number of levels that can be used depends on the memory available on the database server.
Consider an example. You need to view the DepartmentID of an employee whose e-mail address is Sales Agent. To perform this task, you can use the following query:
SELECT DepartmentID FROM HumanResources.EmployeeDepartmentHistory
WHERE BusinessEntityID = /* Level 1 inner query */
(SELECT BusinessEntityID FROM Person.BusinessEntityContact
WHERE ContactTypeID = /* Level 2 inner query */
(SELECT ContactTypeID FROM Person.ContactType WHERE Name= 'Sales Agent')
)
In the preceding example, two queries are nested within another query. The level 2 inner query returns the Contact ID of an employee based on the e-mail address of the employee from the Person table.
The level 1 inner query uses this ContactID to search for the BusinessEntityID of the employee with the given e-mail address. The main query uses the BusinessEntityID returned by level 1 inner query to search for the DepartmentID from the EmployeeDepartmentHistory table.
You can implement subqueries upto 32 levels. However, the number of levels that can be used depends on the memory available on the database server.