Sub Queries With Multiple Levels of Nesting

Let’s go back to sub queries for a bit. You can nest a sub query within a sub query. There really is no limit. But in doing so, you really need to make sure there isn’t a better way, like a join, or Common Table Expression.

The following query finds the names of employees who are also sales persons.

USE AdventureWorks
GO
SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM HumanResources.Employee
     WHERE BusinessEntityID IN
        (SELECT BusinessEntityID
         FROM Sales.SalesPerson)
    )

The innermost query returns the sales person IDs. The query at the next higher level is evaluated with these sales person IDs and returns the contact ID numbers of the employees. Finally, the outer query uses the contact IDs to find the names of the employees.

Like I was saying before, just because you can do it with nested sub queries, doesn’t mean you should. you could accomplish the same query using the following joins.

USE AdventureWorks
GO
SELECT LastName, FirstName
FROM Person.Person c
INNER JOIN HumanResources.Employee e
	ON c.BusinessEntityID = e.BusinessEntityID
INNER JOIN Sales.SalesPerson s
	ON e.BusinessEntityID = s.BusinessEntityID

If you have any questions, send them in! I’m here to help you learn all the SQL you want to learn.