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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *