Jointures

Jointures

Nous avons déjà vu des jointures de table lors de l'utilisation de la clause from avec plusieurs sources. Notamment, nous avons vu que la jointure se fait via un produit cartésien, limité par les critères qu'on précise dans la clause where.

Si on fait plusieurs jointures, la clause where devient longue et difficile à gérer. En plus, contrôler finement le type de jointure demande une attention exacte sur la clause where.

select 
  a.name as Action, 
  c.name as Customer 
from 
  action as a, 
  customer as c, 
  action_customer as ac 
where 
  a.actionId = ac.actionId and 
  ac.customerId = c.customerId;

SQL nous facilite cette tâche avec les clauses type join, qui exécutent les jointures les plus utilisés, et nous permet de les exprimer de façon plus claire. La requête dessus peut être exprimé avec, par exemple, des inner join :

select a.name as Action, c.name as Customer 
from 
  action a
inner join
  action_customer ac on a.actionId = ac.actionId 
inner join
  customer c on ac.customerId = c.customerId;

Avec cette formulation, la phrase est plus lisible, car il est explicitement marqué qu'on est en train de joindre deux tables, et avec quel critère.

Il y a plusieurs types de join à notre disposition.

natural join

Il est possible de joindre deux tables en utilisant les attributs qui partagent le même nom. Cela veut dire qu'on n'a pas besoin de la clause on, parce que les critères de jointure sont implicites.

Par exemple :

select a.name, ac.amount
from action a
natural join action_customer ac;

Ici, les attributs en commun entre les tables action et action_customer sont utilisés comme critères de jointure.

Mais attention ! Nous espérons que la jointure se fait via le lien établi via la clé étrangère (actionId), mais ce n'est pas le cas avec natural join. Il y a en effet, plusieurs colonnes avec le même nom, notamment actionId, createdAtUtc, createdByUtc, createdBy, updatedBy. Le natural join va faire la jointure des lignes seulement si tous les attributs sont identiques entre les deux tables.

Prenons l'exemple de deux tables simples qui représente un pays et une équipe de football.

pays

id	pays	nom	langue
1	FR	France	français
2	ZA	Afrique du Sud	anglais
3	UK	Grande Bretagne	anglais

equipe

pays	equipe	langue
FR	Les bleus	français
ZA	Bafana bafana	xhosa

Et une requête de type natural join :

select *
from pays
natural join equipe;

Le résultat est :

id	pays	nom	langue	equipe
1	FR	France	français	Les bleus

On aurait attendu 2 lignes dans la jointure (pour les deux pays en commun, ZA et FR). En revanche, on voit que la requête nous retourne une seule ligne, car les attributs pays et langue sont utilisés dans la jointure.

Natural join, donc, n'est quasiment jamais utilisé, puisque le risque d'erreur est trop grand.

inner join

Un inner join est typiquement le type de jointure que nous avons utilisés implicitement avec nos clauses from et where. Cette jointure nous retourne uniquement les lignes des 2 tables qui satisfont le critère précisé dans la condition de jointure.

Les lignes qui ne satisfont pas la condition sont exclues de la table gauche et la table droite de la jointure.

Reprenons notre exemple :

pays

id	pays	nom	langue
1	FR	France	français
2	ZA	Afrique du Sud	anglais
3	UK	Grande Bretagne	anglais

equipe

pays	equipe	langue
FR	Les bleus	français
ZA	Bafana bafana	xhosa

Une requête de type inner join nous retourner de résultats plus cohérents :

select *
from pays
inner join equipe on pays.pays = equipe.pays;

Le résultat :

id	pays.pays	nom	pays.langue	equipe.pays	equipe	equipe.langue
1	FR	France	français	FR	Les bleus	français
2	ZA	Afrique du Sud	anglais	ZA	Bafana bafana	xhosa

Notez que le pays UK est exclu du résultat, parce qu'il n'y a pas de ligne correspondant (selon le critère) dans la table equipe.

Vous avez dit de ne jamais utiliser le wildcard *, pourquoi l'utiliser ici ?

C'est juste pour vous indiquer la différence entre natural join et inner join. Vous avez remarqué que natural join nous a retourné une seule colonne pays et une seule colonne langue ? C'est parce que la valeur est identique, donc pourquoi la répéter ?

Avec inner join le critère peut être plus complexe (la clause on peut être un ensemble de conditions avec les opérateurs and, or et not. L'équivalence n'est pas garantie). Donc, on est obligé d'afficher toutes les colonnes de la jointure dans le résultat.

Certains SGBDR permettent l'utilisation du join (sans le mot inner). Personnellement, je préfère être explicite dans mes intentions, et je trouve qu'exprimer inner join et plus parlant de ce qui va se passer dans la requête.

outer join

Il y a souvent la demande de joindre des tables et garder des lignes même si une ligne correspondant ne se trouve pas dans l'autre table.

Pour continuer notre exemple, je veux voir la liste entière de pays, malgré le fait qu'ils ont une équipe ou pas. J'utilise left outer join

select *
from pays
left outer join equipe on pays.pays = equipe.pays;

Le résultat est :

id	pays.pays	nom	pays.langue	equipe.pays	equipe	equipe.langue
1	FR	France	français	FR	Les bleus	français
2	ZA	Afrique du Sud	anglais	ZA	Bafana bafana	xhosa
3	UK	Grande Bretagne	anglais	null	null	null

Avec le left outer join, on garde toutes les lignes de la table gauche (avant l'opérateur left outer join), malgré l'existence d'une ligne correspondante coté droite. On voit que pour le pays UK, il y a des valeurs null à la place d'une équipe.

D'autres exemples des outer join :

• Je veux voir la liste d'articles dans mon catalogue qui avec, en option, le nombre de ventes de l'article (il faut afficher l'article même s'il n'y a pas eu de vente)

• Dans la base saas, je veux voir toutes les interventions et événements. Il faut inclure même les interventions qui n'ont pas d'événement.

Il y a trois sortes de outer join :

• left outer join : Conserver les lignes venant de la relation avant (ou sur la gauche) des mots left outer join, même s’il n’y a pas de correspondance dans l’autre relation. Attention : les lignes venant de l’autre relation (sur la droite) peuvent toujours être exclus !

• right outer join : Conserver les lignes venant de la relation après (ou sur la droite) des mots right outer join, même s’il n’y a pas de correspondance dans l’autre relation. Attention : les lignes venant de la première relation (sur la gauche) peuvent toujours être exclus !

• full outer join : Conserver toutes les lignes des deux relations.

Exercices

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

Utilisez l’opérateur join afin d’afficher le nom, prénom, adresse et code postal de chaque membre de l’équipe (staff).

Solution

Résultat : 2 lignes

select 
  stf.first_name, 
  stf.last_name, 
  adr.address
from staff as stf
inner join address adr
on stf.address_id = adr.address_id; 

Question 2

Utilisez join afin de calculer le montant total encaissé par chaque membre de l’équipe pendant le mois d’août 2005.

Solution

Résultat : 2 lignes

select 
  stf.first_name, 
  stf.last_name, 
  sum(pay.amount)
from 
  staff as stf
inner join payment as pay
  on stf.staff_id = pay.staff_id
where 
  month(pay.payment_date) = 8 and 
  year(pay.payment_date) = 2005
group by 
  stf.first_name, stf.last_name;

Question 3

Listez le nom et prénom de tous les clients (customer). Si un client partage son nom avec un acteur, affichez le nom, prénom et ID de l’acteur aussi (sinon, affichez null).

Solution

Résultat : 620 lignes

select 
  c.first_name, 
  c.last_name, 
  a.actor_id, 
  a.first_name, 
  a.last_name
from customer as c
left outer join actor as a
  on c.last_name = a.last_name;