Header

Subqueries Introduction

Introduction

A subquery is a query that is executed within another query.

Why this is useful, we will look at through some examples.

1. Subquery in `WHERE` statement

We will look at an example from tables movies and roles.

Header

1.1. Question

In this example, we want the following:

A selection of movies from table movies.
Where the length of the name of a movie is greater than the average length of the names of movie roles.

1.2. Average length of the names of movie roles

Table roles contains details of movie roles.

We use functions ROUND() (rounding), AVG() (average), and LENGTH().
To calculate the average length of names of movie roles.

SELECT
  ROUND(AVG(LENGTH(role)))
FROM
  roles;

round
14

The average length is 14 characters.

1.3. Filtering by length of movie names

Table movies contains details of movies.

We use a WHERE statement.
To make a selection based on the length of movie names.
- Where the name is greater than 14 characters.

SELECT
  id,
  name,
  LENGTH(name) AS name_length
FROM
  movies
WHERE
  LENGTH(name) > 14
ORDER BY
  name_length;

id	name	name_length
111813	Few Good Men, A	15
238072	Oceanâ€s Eleven	16
176711	Kill Bill: Vol. 1	17
176712	Kill Bill: Vol. 2	17
192017	Little Mermaid, The	19
194874	Lost in Translation	19
256630	Pirates of the Caribbean	24
297838	Shawshank Redemption, The	25
237431	O Brother, Where Art Thou?	26
257264	Planes, Trains & Automobiles	28

Columns: 3
Rows: 10

We see that only movies with a name longer than 14 characters are selected.

1.4. Problem

Instead of hardcoding the number 14, it would be nice to obtain this with a query.

However, this is not possible just like that.

We need a subquery for this.

1.5. Solution with subquery in `WHERE` statement

We used the following query:

SELECT
  id,
  name,
  LENGTH(name) AS name_length
FROM
  movies
WHERE
  LENGTH(name) > 14
ORDER BY
  name_length;

With the number 14 hardcoded.

In the query below, we replace the number 14 with another query.

The query we used earlier to calculate the number 14.

SELECT
  id,
  name,
  LENGTH(name) AS name_length
FROM
  movies
WHERE
  LENGTH(name) > (
    SELECT
      ROUND(AVG(LENGTH(role)))
    FROM
      roles
  )
ORDER BY
  name_length;

id	name	name_length
111813	Few Good Men, A	15
238072	Oceanâ€s Eleven	16
176711	Kill Bill: Vol. 1	17
176712	Kill Bill: Vol. 2	17
192017	Little Mermaid, The	19
194874	Lost in Translation	19
256630	Pirates of the Caribbean	24
297838	Shawshank Redemption, The	25
237431	O Brother, Where Art Thou?	26
257264	Planes, Trains & Automobiles	28

Columns: 3
Rows: 10

We see the same result as before.
This shows that we can execute a query within a query.
- This is called a subquery.

2. Subquery in `FROM` statement

We just looked at a subquery from the WHERE statement.

This allowed us to use the result of a subquery in filtering.

2.1. Question and problem

We compared the length of a movie name with the average length of movie role names.

Now we want to add that average length as a column.
However, this is not possible just like that.
- Because it is only available in the WHERE statement.

2.2. Solution with subquery in `FROM` statement