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.