560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
SQL subqueries (including correlated subqueries)
Part one of a four-part series of blogs
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!
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.
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.
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.
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.
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.
|Parts of this blog|
25 Aytoun Street