Opérations de set

Les opérations de Set

On considère le résultat de chaque phrase select une nouvelle table (relation). On peut donc fusionner plusieurs relations en utilisant les opérateurs suivants :

• Union : fusionner deux sets, en gardant tous les tuples uniques provenant des 2 sets

• Intersect : fusionner deux sets, en gardant les tuples qui se trouvent dans les 2 sets

• Except : fusionner deux sets, en gardant les tuples du premier set qui ne se trouvent pas dans le 2ème set

Les opérateurs de set sont des outils qui permettent :

• De créer des requêtes plus lisibles (plusieurs requêtes simples, fusionnées)

• D'optimiser nos requêtes : éviter des produits cartésiens énormes, et fusionner le résultat

Union

Je veux savoir les villes de France où l'on a facturé plus que 1000 € pour l'année 2019 ou 2022.

Une façon de traiter cette demande serait de construire deux requêtes simples, et puis retourner l'union entre les 2 sets des résultats.

Par exemple :

select trim(address_city) as city 
from action 
where year(startUtc) = 2019 and budget > 1000;
/* Retourne 113 lignes avec des doublons */

Et :

select trim(address_city) as city 
from action 
where year(startUtc) = 2022 and budget > 1000;
/* Retourne 1354 lignes avec des doublons */

Je peux fusionner les 2 tableaux générés avec l'opérateur union, qui traitera les deux tableaux comme des sets. C'est-à-dire, il n'y a pas de doublon !

(select trim(address_city) as city 
from action 
where year(startUtc) = 2019 and budget > 1000)
union
(select trim(address_city) as city 
from action 
where year(startUtc) = 2022 and budget > 1000);
/* Retourne 787 lignes, sans doublon ! */

Par défaut, union enlève les doublons dans le résultat. En revanche, on peut aussi utiliser union all afin de garder les doublons.

Intersection

Je veux modifier ma requête. Je veux connaître les villes dans lesquelles on a facturé plus que 1000 € pour l'année 2019 et l'année 2022.

Pour cela, pas besoin de créer une série de conditions complètes ! On a juste à utiliser l'opérateur intersect, qui va garder uniquement les tuples qui se trouvent dans les 2 sets.

(select trim(address_city) as city
from action 
where year(startUtc) = 2019 and budget > 1000)
intersect
(select trim(address_city) as city
from action 
where year(startUtc) = 2022 and budget > 1000);
/* Retourne 43 lignes, sans doublon ! */

Il y a donc 43 villes où on a facturé plus que 1000 € sur le deux années.

Except

Je veux savoir quelles villes ont facturé plus que 1000 € en 2019, mais pas en 2022. Il faut donc enlever du premier set toutes les villes qui se trouvent aussi dans le deuxième set.

(select trim(address_city) as city
from action 
where year(startUtc) = 2019 and budget > 1000)
except
(select trim(address_city) as city
from action 
where year(startUtc) = 2022 and budget > 1000);
/* Retourne 43 lignes, sans doublon ! */

Exercice

Les exercices suivants sont basés sur la base de données SAKILA, une base de données de test fourni par MySQL.

Récupérez la base SAKILA ici.

Question 1

Un client vient chercher un film avec l’acteur « Groucho Dunst ». Elle aime les films d’action.

Utilisez l’opérateur union afin de présenter une liste de films qui répond à, au moins, un des deux critères.

Solution

95 lignes.

(select title
from film, film_actor, actor
where 
  film.film_id = film_actor.film_id and 
  actor.actor_id = film_actor.actor_id and 
  actor.first_name = "Groucho" and 
  actor.last_name = "Dunst")
union
(select title
from film, film_category, category
where 
  film.film_id = film_category.film_id and 
  film_category.category_id = category.category_id and 
  category.name = "Action");

Question 2

Un client vient chercher un film avec l’acteur « Groucho Dunst ». Elle aime les films d’action.

Utilisez l’opérateur intersect afin d’afficher uniquement la liste de films d’action avec « Groucho Dunst »

Solution

4 lignes.

(select title
from film, film_actor, actor
where 
  film.film_id = film_actor.film_id and 
  actor.actor_id = film_actor.actor_id and 
  actor.first_name = "Groucho" and 
  actor.last_name = "Dunst")
intersect
(select title
from film, film_category, category
where 
  film.film_id = film_category.film_id and 
  film_category.category_id = category.category_id and 
  category.name = "Action");

Question 3

Vous mettez en place une promo pour les films non-adulte. En utilisant l’opérateur except, trouvez tous les films qui coûtent moins que 4€ à louer, en excluant ceux du rating « R ». Adaptez votre requête afin d’afficher les prix avec 10% de remise.

Solution

534 lignes.

(select title, (rental_rate * 0.9) as promo, rating 
from film 
where 
  rental_rate < 4)
except
(select title, (rental_rate * 0.9) as promo, rating 
from film 
where 
  rating = "R");