BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
If you’ve been writing SQL queries for a while you’re probably fairly confident with writing single SELECT statements to return a set of records. If you’re ready for a bit more of a challenge, this blog will teach you how to nest one SELECT statement inside another in order to create a subquery. If you’re thinking “that sounds simple enough” then you might want to read to the end of this series, where we’ll attempt to melt your brain by explaining correlated subqueries!
- Creating a Simple Subquery (this blog)
- Subqueries and Aggregate Functions
- Using ALL, ANY, SOME and IN with Subqueries
- Correlated Subqueries
Posted by Andrew Gould on 28 January 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating a Simple Subquery
The concept of a subquery is fairly straightforward - it's simply one query nested inside another. You usually find a subquery in the WHERE or SELECT clauses of a query, although you can use them anywhere an expression is allowed.
To demonstrate the basics of subqueries we'll work step-by-step through a simple example. Let's imagine that in a table of films we want to see which film or films have the highest number of Oscar wins. The problem is that we don't know what the highest number of Oscar wins is. However, we can use a subquery to find out.
Writing a Basic Query
Let's start by writing a basic query to find the highest number of Oscar wins in the table:
The important feature of this query is that the result is a single number:
The result of running the query above.
Adding Extra Columns to the Query
It would be useful at this point to add some extra details to the query so that we could see, for example, the name of the film with this number of Oscar wins. However, if we try to add this to the SELECT list we'll find that we return an error:
This is the error message we receive when we run the query.
The reason the query fails is because we're trying to mix an aggregated value (the result of the MAX function) with detail records, and that's something that you're simply not allowed to do.
Enclosing a Query in Parentheses
The solution to the problem shown above is to turn the original query into a subquery. We can then add it to the WHERE clause of another query. To do this, first we need to add a set of parentheses to enclose the original query:
A subquery must always be enclosed in a set of parentheses.
Building the Outer Query
Once the subquery is contained within a set of parentheses we can then build the outer query around it:
When we execute the query the subquery is processed first, followed by the outer query. This results in a list of films whose FilmOscarWins field is equal to the highest FilmOscarWins from the table:
In this case there are two records in the result of the query.
Microsoft say that you nest up to 32 levels of subquery. Hopefully, you'll never find yourself in a situation where you'll have to do this!
Now that you know what a basic subquery looks like we can move on and see what other useful things they can do. The next step is to investigate some of the other aggregate functions that we can usefully combine with a subquery.