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 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! Creating a Simple Subquery (this blog) Subqueries and Aggregate Functions 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 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!

Creating a Simple Subquery (this blog)
Subqueries and Aggregate Functions
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

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.

Parts of this blog
Creating a Simple Subquery (this blog) Subqueries and Aggregate Functions 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

Creating a Simple Subquery

Writing a Basic Query

Adding Extra Columns to the Query

Enclosing a Query in Parentheses

Building the Outer Query

What's Next?

Head office

London

Manchester