This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Creating a Simple Subquery Subqueries and Aggregate Functions Using ALL, ANY, SOME and IN with Subqueries (this blog) Correlated Subqueries This article is part of our complete SQL Tutorial, which is an alternative to booking classroom-based SQL courses with Wise Owl.

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!

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

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:

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.

What's Next?

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

Parts of this blog
Creating a Simple Subquery Subqueries and Aggregate Functions Using ALL, ANY, SOME and IN with Subqueries (this blog) Correlated Subqueries

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

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

What's Next?

Head office

London

Manchester