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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.glassworks.tech/sgbdr/interrogation/025-jointures.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.