SQL Server nested select is also known as a “sub query”. This is where instead of using a list, table, or view, you actually use another query. In this article, we will go over the basics on how to use a SQL Server nested select statement.
Basic Rules For A Nested Select
Here are a couple basic rules when using a nested select statement in SQL Server.
- The nested select statement must be enclosed in parentheses.
- When using an ORDER BY clause in your nested select, you must specify TOP command.
- Many times, you will need to alias the nested select.
Sample Nested Select
Here is a sample where we return the basic animal information with their average weight.
SELECT A.AnimalID, A.AnimalName, W. AverageWeight FROM Animal A INNER JOIN (SELECT AnimalID, AVG(AnimalWeight) AS AverageWeight FROM AnimalWeights GROUP BY AnimalID) W ON W.AnimalID = A.AnimalID
In the above example, we do our normal inner join, but we join to a nested select statement. This allows you to join on the results from the nested select. You can see that the nested select statement will get the average weight of the animals by AnimalID. That whole query is inside parenthesis. Then we alias that nested select results as “W”. We can then reference the 2 return values (AnimalID and AverageWeight) outside of the nested select statement by using the table alias W.
NOTE: Remember that you will need to alias your nested select statements most of the time. This can stop you up if you aren’t looking for it.