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 two 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 (this blog) Using ALL, ANY, SOME and IN with Subqueries 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 two 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 (this blog)
Using ALL, ANY, SOME and IN with Subqueries
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

Subqueries and Aggregate Functions

In the first part of this series we saw a simple example of a subquery which included the MAX function. Subqueries are often used with aggregate functions such as MAX, and this part of the series will show a couple of the other useful functions that you can use.

A Reminder of the Aggregate Functions

You might find this blog on grouping data useful as a reminder of which aggregate functions are available in SQL.

Finding the Highest or Lowest Value in a Column

We've already seen how to use the MAX function to match records against the largest value in a column. You can also use the MIN function to find the smallest value in a field. The query below would find the film or films with the shortest running time:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes =

(SELECT

MIN(FilmRunTimeMinutes)

FROM

tblFilm)

The results of this query are shown below:

Short duration, long title!

Finding Records Above or Below Average

You can use the AVG function in a subquery to return the average of a column of numbers. You can then combine this with the greater-than and less-than operators to find records that are above or below average. The query below shows all of the films whose running time is longer than the average running time of all the films in the table:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes >

(SELECT

AVG(FilmRunTimeMinutes)

FROM

tblFilm)

Adding More Criteria

You can add further criteria to the WHERE clause of both the inner and outer queries to make your queries even more specific. The example below looks for all films which have been released since the year 2000, and whose budget is greater than the highest budget of any film released before the year 2000.

SELECT

FilmName

,FilmBudgetDollars

FROM

tblFilm

WHERE

FilmReleaseDate >= '2000-01-01'

AND

FilmBudgetDollars >

(SELECT

MAX(FilmBudgetDollars)

FROM

tblFilm

WHERE

FilmReleaseDate < '2000-01-01')

The results of the query are shown below:

Three films in our database cost more to make than the most expensive film made before the year 2000.

Technically, we don't need the criterion which asks for films released on or after January 1st 2000. The only films that could be more expensive than the most expensive film made prior to that date must have been made after that date! I've included it here just to show how to use multiple criteria.

What's Next?

Now that you've seen how to use basic criteria with subqueries it's time to take a look at some of the more exotic comparisons using keywords such as ALL, ANY and IN.

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

Subqueries and Aggregate Functions

A Reminder of the Aggregate Functions

Finding the Highest or Lowest Value in a Column

Finding Records Above or Below Average

Adding More Criteria

What's Next?

Head office

London

Manchester