SQL subqueries (including correlated subqueries)
Part three 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
  2. Subqueries and Aggregate Functions
  3. Using ALL, ANY, SOME and IN with Subqueries (this blog)
  4. Correlated Subqueries

This article is part of our complete SQL Tutorial, which is an alternative to booking classroom-based SQL courses with Wise Owl.

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.

Using ALL, ANY, SOME and IN with Subqueries

So far in this blog, all of our subqueries have returned a single value which we have then compared against a field in a table.  It's also possible for a subquery to return multiple values that can be compared against another column, and this part of the series shows you how to use this type of subquery.

Returning Multiple Values in a Subquery

It isn't difficult to make a subquery return multiple values; all you need is a select statement which returns multiple records.  In the example below the subquery returns a list of birth dates from the table of actors in our database:

SELECT

DirectorName

,DirectorDOB

FROM

tblDirector

WHERE

DirectorDOB <

(SELECT

ActorDOB

FROM

tblActor)

Returning multiple values in a subquery is easy, but the above example will cause an error when we attempt to execute it:

Error message

Part of the error message you would see if you attempted to run the above query.

The reason for the error is that the operator by itself can't be used to compare multiple values.  To make the above example work we need to add another keyword to the comparison.

Using the ALL Keyword

One choice of extra keyword we could add to the above query is ALL.  In the example shown below we are looking for directors whose date of birth is before that of all of the actors:

SELECT

DirectorName

,DirectorDOB

FROM

tblDirector

WHERE

DirectorDOB < ALL

(SELECT

ActorDOB

FROM

tblActor)

The result of this query is a list of directors who are older than every actor:

Old directors

These three directors are older than all of the actors.

 

You'll probably have spotted that this query is the same as asking for directors whose date of birth is less than the minimum date of birth in the actor table.  We could have achieved the same result using the MIN function and without using ALL.

Using ANY or SOME

The ANY and SOME keywords are interchangeable and allow you to check if the value of a field exceeds any one of the results returned by a subquery.  The example below looks for directors whose date of birth is before that of any of the actors:

SELECT

DirectorName

,DirectorDOB

FROM

tblDirector

WHERE

DirectorDOB < ANY

(SELECT

ActorDOB

FROM

tblActor)

In plain English what we'll return is a list of directors who are older than the youngest actor:

Directors older than any actor

As it turns out, all of the directors are older than at least one of the actors.

 

Using the IN Keyword

You can use the IN keyword to test if the value of a field matches an item in the results of a subquery.  The example below shows a list of directors whose name also appears in the actor table:

SELECT

DirectorName

FROM

tblDirector

WHERE

DirectorName IN

(SELECT

ActorName

FROM

tblActor)

The results of the query are shown below:

Actors and directors

Seven people in the database have been both an actor and a director.

 

You can also use NOT IN to find all of the values in a field which do not have a match in the results of a subquery.

What's Next?

The final part of this series looks at how to use correlated subqueries.  Not for the faint-hearted!

  1. Creating a Simple Subquery
  2. Subqueries and Aggregate Functions
  3. Using ALL, ANY, SOME and IN with Subqueries (this blog)
  4. Correlated Subqueries
This blog has 0 threads Add post