Consultas con EXISTS

Toma de decisiones basada en datos en SQL

Irene Ortner

Data Scientist at Applied Statistics

EXISTS

Caso especial de consulta anidada correlacionada.
Sirve para comprobar si el resultado de una anidada correlacionada está vacío.
Devuelve: TRUE o FALSE
TRUE = no vacío -> se selecciona la fila de la consulta externa.
FALSE = vacío
Las columnas en SELECT no importan: usa SELECT *

Películas con al menos una valoración

SELECT *
FROM movies AS m
WHERE EXISTS
    (SELECT *
     FROM renting AS r
     WHERE rating IS NOT NULL 
     AND r.movie_id = m.movie_id);

Películas con al menos una valoración

SELECT *
FROM renting AS r
WHERE rating IS NOT NULL 
AND r.movie_id = 11;

| renting_id | customer_id | movie_id | rating | renting_price |
|------- ----|-------------|----------|--------|---------------|

Películas con al menos una valoración

SELECT *
FROM renting AS r
WHERE rating IS NOT NULL 
AND r.movie_id = 1;

| renting_id | customer_id | movie_id | rating | renting_price |
|------------|-------------|----------|--------|---------------|
| 71         | 111         | 1        | 5      | 2018-07-21    |
| 170        | 36          | 1        | 10     | 2018-10-18    |

Consulta con EXISTS y resultado

SELECT *
FROM movies AS m
WHERE EXISTS
    (SELECT *
     FROM renting AS r
     WHERE rating IS NOT NULL 
     AND r.movie_id = m.movie_id);

| movie_id | title                 | genre  | runtime | year_of_release | renting_price |
|----------|-----------------------|--------|---------|-----------------|---------------|
| 1        | One Night at McCool's | Comedy | 93      | 2001            | 2.09          |
| 2        | Swordfish             | Drama  | 99      | 2001            | 2.19          |

NOT EXISTS

TRUE = la tabla está vacía -> se selecciona la fila de la consulta externa.

SELECT *
FROM movies AS m
WHERE NOT EXISTS
    (SELECT *
     FROM renting AS r
     WHERE rating IS NOT NULL 
     AND r.movie_id = m.movie_id);

| movie_id | title    | genre  | runtime | year_of_release | renting_price |
|----------|----------|--------|---------|-----------------|---------------|
| 11       | Showtime | Comedy | 95      | 2002            | 1.79          |

¡Vamos a practicar!

Toma de decisiones basada en datos en SQL