Consulta aninhada

Tomada de decisão orientada por dados em SQL

Irene Ortner

Data Scientist at Applied Statistics

Consulta aninhada

Bloco SELECT em WHERE ou HAVING
Consulta interna retorna um ou vários valores
Usa o resultado da interna para filtrar linhas na outra consulta

A consulta interna

Etapa 1: consulta interna

SELECT DISTINCT customer_id
FROM renting
WHERE rating <= 3

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

Resultado na cláusula WHERE

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

A consulta externa

Etapa 2: consulta externa

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

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

Consulta aninhada na cláusula HAVING

Etapa 1: consulta interna

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

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

Consulta aninhada na cláusula HAVING

Etapa 2: consulta externa

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 |

Quem são os atores do filme 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      |

Vamos praticar!

Tomada de decisão orientada por dados em SQL