This page has 0 threads | Add post

Creating a Simple Subquery

Written by Andrew Gould

In this tutorial

Writing a Basic Query
Adding Extra Columns to the Query
Enclosing a Query in Parentheses
Building the Outer Query

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!

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.

Previous
tutorial

Views

Previous
part

Top of page

Next
part

Subqueries and Aggregate Functions

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

Creating a Simple Subquery

Writing a Basic Query

Adding Extra Columns to the Query

Enclosing a Query in Parentheses

Building the Outer Query

Head office

London

Manchester