> For the complete documentation index, see [llms.txt](https://docs.glassworks.tech/sgbdr/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.glassworks.tech/sgbdr/interrogation/025-jointures.md).

# 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`.

```sql
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` :

```sql
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 :

```sql
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` :

```sql
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 :

```sql
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`.

{% hint style="warning" %}
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.
{% endhint %}

{% hint style="success" %}
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.
{% endhint %}

### `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`

```sql
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.](https://dev.glassworks.tech:18081/courses/sgbdr/sgbdr-supports/-/tree/main/src/samples/sakila)

### 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`).

<details>

<summary>Solution</summary>

Résultat : 2 lignes

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

</details>

### Question 2

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

<details>

<summary>Solution</summary>

Résultat : 2 lignes

```sql
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;
```

</details>

### 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`).

<details>

<summary>Solution</summary>

Résultat : 620 lignes

```sql
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;
```

</details>