Subqueries with IN, ANY, and ALL

Introduction

With a subquery using EXISTS, you filter based on the occurrence of a certain match.

In addition, there are other possibilities:

• IN
  • Filtering on any occurrence (in a list).
• ANY
  • Filtering on any occurrence.
• ALL
  • Filtering on every occurrence.

---

1. EXISTS

In the previous module, a subquery with EXISTS was covered.

Let's briefly revisit it.

We created the following query:

SELECT
  *
FROM
  movies
WHERE
  EXISTS (
    SELECT
      *
    FROM
      movies_genres
    WHERE
      movies.id = movies_genres.movie_id
      AND genre = 'Horror'
  );

id	name	year	rank
10920	Aliens	1986	8.2
147603	Hollow Man	2000	5.3
314965	Stir of Echoes	1999	7

Columns: 4
Rows: 3

This query returns only movies with the genre Horror.

---

2. IN with subquery

With the IN statement and a subquery, we do the following:

• Filtering on any occurrence (in a list).

We use this to achieve the same result as before:

• Only movies with the genre Horror.

SELECT
  *
FROM
  movies
WHERE
  movies.id IN (
    SELECT
      movie_id
    FROM
      movies_genres
    WHERE
      genre = 'Horror'
  );

id	name	year	rank
10920	Aliens	1986	8.2
147603	Hollow Man	2000	5.3
314965	Stir of Echoes	1999	7

Columns: 4
Rows: 3

We see the same result as earlier with:

• Subquery with EXISTS.

We now do the following:

• The subquery retrieves a list of movie_ids that have the genre 'Horror'.
• With IN, we filter only the ids from this list.

---

3. ANY with subquery

With the ANY statement and a subquery, we do the following:

• Filtering on any occurrence.

We use this to achieve the same result as before:

• Only movies with the genre Horror.

SELECT
  *
FROM
  movies
WHERE
  movies.id = ANY (
    SELECT
      movie_id
    FROM
      movies_genres
    WHERE
      genre = 'Horror'
  );

id	name	year	rank
10920	Aliens	1986	8.2
147603	Hollow Man	2000	5.3
314965	Stir of Echoes	1999	7

Columns: 4
Rows: 3

We see the same result as earlier with:

• Subquery with EXISTS.
• Subquery with IN.

We now do the following:

• The subquery retrieves a list of movie_ids that have the genre 'Horror'.
• With ANY, we filter only the ids from this list.

---

4. ALL with subquery

With the ALL statement and a subquery, we do the following:

• Filtering on every occurrence.

We use this for a new example:

• Only the movie that solely has the genre War.

SELECT
  *
FROM
  movies
WHERE
  movies.id = ALL (
    SELECT
      movie_id
    FROM
      movies_genres
    WHERE
      genre = 'War'
  );

id	name	year	rank
46169	Braveheart	1995	8.3

Columns: 4
Rows: 1

The movie Braveheart is thus the only movie with the genre 'War'.

We now do the following:

• The subquery retrieves a list of movie_ids that have the genre 'War'.
  • This is only one id.
• With ALL, we filter the ids from this list.
  • On every/complete match with the list.

The use of ALL may seem somewhat abstract/unhandy to you. You will not often use this in practice.

---

Summary

• IN
  • Filtering on any occurrence (in a list).
• ANY
  • Filtering on any occurrence.
• ALL
  • Filtering on every occurrence.