Nested query

Data-Driven Decision Making in SQL

Irene Ortner

Data Scientist at Applied Statistics

Nested query

SELECT block in WHERE or HAVING clauses
Inner query returns single or multiple values
Use result from the inner query to select specific rows in another query

The inner query

Step 1: The inner query

SELECT DISTINCT customer_id
FROM renting
WHERE rating <= 3

| customer_id |
|-------------|
| 28          |
| 41          |
| 86          |
| 120         |

Result in the WHERE clause

SELECT name
FROM customers
WHERE customer_id IN (28, 41, 86, 120);

The outer query

Step 2: The outer query

SELECT name
FROM customers
WHERE customer_id IN
    (SELECT DISTINCT customer_id
    FROM renting
    WHERE rating <= 3);

| name            |
|-----------------|
| Sidney Généreux |
| Zara Mitchell   |

Nested query in the HAVING clause

Step 1: The inner query

SELECT MIN(date_account_start)
FROM customers
WHERE country = 'Austria';

| min        |
|------------|
| 2017-11-22 |

Nested query in the HAVING clause

Step 2: The outer query

SELECT country, MIN(date_account_start)
FROM customers
GROUP BY country
HAVING MIN(date_account_start) <
    (SELECT MIN(date_account_start)
    FROM customers
    WHERE country = 'Austria');

| country       | min        |
|---------------|------------|
| Spain         | 2017-02-14 |
| Great Britain | 2017-03-31 |

Who are the actors in the movie Ray?

SELECT name
FROM actors
WHERE actor_id IN
    (SELECT actor_id 
     FROM actsin
     WHERE movie_id =
        (SELECT movie_id 
         FROM movies
         WHERE title='Ray'));

| name             |
|------------------|
| Jamie Foxx       |
| Kerry Washington |
| Regina King      |

Let's practice!

Data-Driven Decision Making in SQL