This page has 0 threads | Add post

Using ALL, ANY, SOME and IN with Subqueries

Written by Andrew Gould

In this tutorial

Returning Multiple Values in a Subquery
Using the ALL Keyword
Using ANY or SOME
Using the IN Keyword

So far in this tutorial 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:

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:

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:

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:

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.

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

Previous
tutorial

Views

Previous
part

Subqueries and Aggregate Functions

Top of page

Next
part

Correlated Subqueries

Next
tutorial

CTEs and derived tables

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Subqueries

Using ALL, ANY, SOME and IN with Subqueries

Returning Multiple Values in a Subquery

Using the ALL Keyword

Using ANY or SOME

Using the IN Keyword

Head office

London

Manchester