Queries met EXISTS

Data-driven beslissingen nemen met SQL

Irene Ortner

Data Scientist at Applied Statistics

EXISTS

Speciaal geval van een gecorreleerde geneste query.
Gebruikt om te checken of het resultaat van een gecorreleerde geneste query leeg is.
Geeft: TRUE of FALSE
TRUE = niet leeg -> rij van de buitenste query wordt gekozen.
FALSE = leeg
Kolommen in SELECT tellen niet mee - gebruik SELECT *

Films met minstens één rating

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

Films met minstens één rating

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

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

Films met minstens één rating

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    |

EXISTS-query met resultaat

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 = tabel is leeg -> rij van de buitenste query wordt gekozen.

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          |

Laten we oefenen!

Data-driven beslissingen nemen met SQL