WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 520 reviews for our classroom and online training
If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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!

1. Creating a Simple Subquery (this blog)
2. Subqueries and Aggregate Functions
3. Using ALL, ANY, SOME and IN with Subqueries
4. 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:

SELECT

MAX(FilmOscarWins)

FROM

tblFilm

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:

(SELECT

MAX(FilmOscarWins)

FROM

tblFilm)

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:

SELECT

FilmName

,FilmReleaseDate

,FilmOscarWins

FROM

tblFilm

WHERE

FilmOscarWins =

(SELECT

MAX(FilmOscarWins)

FROM

tblFilm)

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!

## What's Next?

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.