---
jupytext:
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.16.0
kernelspec:
  name: python3
  display_name: Python 3
---

# Jointures

```{code-cell} python
:tags: [hide-input]

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import numpy as np
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)
```

## Pourquoi les jointures ?

Le modèle relationnel décompose l'information en tables distinctes pour éliminer la redondance. Mais pour répondre aux questions métier, on a souvent besoin de combiner plusieurs tables. C'est le rôle des **jointures** : elles reconstituent temporairement les informations provenant de tables différentes en les associant selon une condition logique.

```{prf:definition} Jointure
:label: ch05-def-jointure
Une **jointure** est une opération qui combine les lignes de deux (ou plusieurs) tables selon une **condition de jointure** (le plus souvent l'égalité de valeurs entre une clé étrangère et une clé primaire). Le résultat est une table virtuelle dont les colonnes sont la concaténation des colonnes des tables source.

En algèbre relationnelle, la jointure naturelle $R \bowtie S$ produit tous les tuples du produit cartésien $R \times S$ qui satisfont la condition.
```

---

## Mise en place du jeu de données

```{code-cell} python
conn = sqlite3.connect(":memory:")

# Schéma — executescript désactive les FK (COMMIT implicite), on les réactive ensuite
conn.executescript("""
CREATE TABLE departements (
    dept_id  INTEGER PRIMARY KEY,
    nom      TEXT    NOT NULL,
    ville    TEXT    NOT NULL
);
CREATE TABLE employes (
    emp_id   INTEGER PRIMARY KEY,
    prenom   TEXT    NOT NULL,
    nom      TEXT    NOT NULL,
    salaire  REAL    NOT NULL,
    dept_id  INTEGER REFERENCES departements(dept_id)
);
CREATE TABLE projets (
    projet_id  INTEGER PRIMARY KEY,
    nom        TEXT    NOT NULL,
    budget     REAL    NOT NULL,
    dept_id    INTEGER REFERENCES departements(dept_id)
);
CREATE TABLE employe_projet (
    emp_id     INTEGER REFERENCES employes(emp_id),
    projet_id  INTEGER REFERENCES projets(projet_id),
    role       TEXT    NOT NULL DEFAULT 'Membre',
    PRIMARY KEY (emp_id, projet_id)
);
""")

conn.execute("PRAGMA foreign_keys = ON")

# Données — ordre respectant les FK : departements → employes → projets → employe_projet
conn.executemany("INSERT INTO departements VALUES (?,?,?)", [
    (10, 'Informatique', 'Paris'),
    (20, 'Comptabilité', 'Lyon'),
    (30, 'Marketing',    'Bordeaux'),
    (40, 'Logistique',   'Nantes'),
])
conn.executemany("INSERT INTO employes VALUES (?,?,?,?,?)", [
    (1, 'Alice',   'Martin', 4500, 10),
    (2, 'Bob',     'Dupont', 3800, 20),
    (3, 'Charlie', 'Leroy',  5200, 10),
    (4, 'Diana',   'Morel',  4100, 30),
    (5, 'Eric',    'Blanc',  4700, 20),
    (6, 'Fatima',  'Ahmed',  5500, 10),
    (7, 'Gilles',  'Petit',  3600, None),  # sans département
])
conn.executemany("INSERT INTO projets VALUES (?,?,?,?)", [
    (101, 'Refonte ERP',       250000, 10),
    (102, 'Audit fiscal',       80000, 20),
    (103, 'Campagne social',   120000, 30),
    (104, 'Portail client',    180000, 10),
])
# Projet orphelin : dept_id=50 n'existe pas — on commit avant de changer les FK
conn.commit()
conn.execute("PRAGMA foreign_keys = OFF")
conn.execute("INSERT INTO projets VALUES (105, 'Optimisation stock', 60000, 50)")
conn.commit()
conn.execute("PRAGMA foreign_keys = ON")

conn.executemany("INSERT INTO employe_projet VALUES (?,?,?)", [
    (1, 101, 'Chef de projet'),
    (3, 101, 'Développeur'),
    (6, 101, 'Architecte'),
    (2, 102, 'Analyste'),
    (4, 103, 'Responsable'),
    (1, 104, 'Développeur'),
    (3, 104, 'Chef de projet'),
])
conn.commit()
print("Jeu de données créé.")
print("Employés :", conn.execute("SELECT COUNT(*) FROM employes").fetchone()[0])
print("Projets  :", conn.execute("SELECT COUNT(*) FROM projets").fetchone()[0])
```

---

## INNER JOIN

```{prf:definition} INNER JOIN (jointure interne)
:label: ch05-def-inner-join
L'**INNER JOIN** retourne uniquement les lignes pour lesquelles la condition de jointure est satisfaite dans **les deux tables**. Les lignes sans correspondance de l'un ou l'autre côté sont exclues.

Syntaxe : `FROM table1 INNER JOIN table2 ON table1.col = table2.col`

Le mot-clé `INNER` est optionnel : `JOIN` seul est équivalent à `INNER JOIN`.
```

```{prf:example} Équi-jointure
:label: ch05-ex-equijointure
L'**équi-jointure** est le cas particulier (et de loin le plus fréquent) où la condition est une égalité : `ON e.dept_id = d.dept_id`. Elle correspond à la jointure naturelle quand les colonnes ont le même nom. On peut aussi utiliser `USING(dept_id)` comme raccourci quand les colonnes ont le même nom dans les deux tables.
```

```{code-cell} python
# INNER JOIN : employes avec leur département (exclut Gilles, sans dept)
df = pd.read_sql("""
SELECT e.emp_id, e.prenom, e.nom, e.salaire,
       d.nom AS departement, d.ville
FROM employes e
INNER JOIN departements d ON e.dept_id = d.dept_id
ORDER BY d.nom, e.nom
""", conn)
print(f"INNER JOIN — {len(df)} lignes (Gilles exclu car dept_id NULL)")
print(df.to_string(index=False))
```

---

## LEFT JOIN

```{prf:definition} LEFT JOIN (jointure gauche)
:label: ch05-def-left-join
Le **LEFT JOIN** (ou LEFT OUTER JOIN) retourne **toutes les lignes de la table gauche** (première table mentionnée), auxquelles sont associées les lignes correspondantes de la table droite quand elles existent. Si aucune correspondance n'existe, les colonnes de la table droite sont remplies avec `NULL`.

Le LEFT JOIN est utile pour trouver les lignes "sans correspondance" dans la table droite : il suffit d'ajouter `WHERE table_droite.col IS NULL`.
```

```{code-cell} python
# LEFT JOIN : tous les employés, même sans département
df = pd.read_sql("""
SELECT e.prenom, e.nom, e.salaire,
       COALESCE(d.nom, '(sans département)') AS departement
FROM employes e
LEFT JOIN departements d ON e.dept_id = d.dept_id
ORDER BY departement, e.nom
""", conn)
print(f"LEFT JOIN — {len(df)} lignes (tous les employés, y compris Gilles)")
print(df.to_string(index=False))
```

```{code-cell} python
# Anti-jointure : employés SANS département
df_sans = pd.read_sql("""
SELECT e.prenom, e.nom
FROM employes e
LEFT JOIN departements d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL
""", conn)
print("Anti-jointure : employés sans département")
print(df_sans.to_string(index=False))
```

---

## RIGHT JOIN et FULL OUTER JOIN

```{prf:definition} RIGHT JOIN
:label: ch05-def-right-join
Le **RIGHT JOIN** (RIGHT OUTER JOIN) est le symétrique du LEFT JOIN : il retourne **toutes les lignes de la table droite**, avec `NULL` pour les colonnes de la table gauche quand aucune correspondance n'existe.

SQLite ne supporte pas nativement le RIGHT JOIN — on peut le simuler en inversant l'ordre des tables dans un LEFT JOIN.
```

```{prf:definition} FULL OUTER JOIN
:label: ch05-def-full-join
Le **FULL OUTER JOIN** retourne **toutes les lignes des deux tables**. Les lignes sans correspondance reçoivent `NULL` dans les colonnes de l'autre table. Il combine le résultat du LEFT JOIN et du RIGHT JOIN.

SQLite ne supporte pas le FULL OUTER JOIN nativement. On peut le simuler avec `LEFT JOIN UNION ALL LEFT JOIN` inversé.
```

```{code-cell} python
# Simulation du FULL OUTER JOIN dans SQLite
# (toutes les combinaisons employe <-> departement, même sans correspondance)
full_outer_query = """
SELECT e.prenom, e.nom, d.nom AS departement
FROM employes e
LEFT JOIN departements d ON e.dept_id = d.dept_id

UNION ALL

SELECT NULL AS prenom, NULL AS nom, d.nom AS departement
FROM departements d
LEFT JOIN employes e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL
"""
df_full = pd.read_sql(full_outer_query, conn)
print(f"FULL OUTER JOIN simulé — {len(df_full)} lignes")
print(df_full.to_string(index=False))
```

---

## CROSS JOIN

```{prf:definition} CROSS JOIN (produit cartésien)
:label: ch05-def-cross-join
Le **CROSS JOIN** produit le **produit cartésien** des deux tables : chaque ligne de la table gauche est combinée avec chaque ligne de la table droite. Si la table gauche a $m$ lignes et la droite $n$ lignes, le résultat a $m \times n$ lignes.

Il n'y a pas de condition `ON`. Le CROSS JOIN est rarement utilisé en production mais utile pour générer des combinaisons (ex. : grille de disponibilités, calendrier).
```

```{prf:remark}
:label: ch05-rem-cross-join
Un `INNER JOIN` sans clause `ON` (ou avec une condition toujours vraie) est équivalent à un CROSS JOIN. L'ancienne syntaxe avec des virgules dans `FROM` produisait un produit cartésien : `FROM employes, departements` — aujourd'hui dépréciée, car elle mène facilement à des jointures oubliées.
```

```{code-cell} python
# CROSS JOIN : toutes les combinaisons (employé, département)
df_cross = pd.read_sql("""
SELECT e.prenom AS employe, d.nom AS departement
FROM employes e
CROSS JOIN departements d
ORDER BY e.prenom, d.nom
LIMIT 12
""", conn)
n_emp = conn.execute("SELECT COUNT(*) FROM employes").fetchone()[0]
n_dept = conn.execute("SELECT COUNT(*) FROM departements").fetchone()[0]
print(f"CROSS JOIN : {n_emp} × {n_dept} = {n_emp * n_dept} combinaisons (12 premières affichées)")
print(df_cross.to_string(index=False))
```

---

## SELF JOIN

```{prf:definition} SELF JOIN (auto-jointure)
:label: ch05-def-self-join
Un **SELF JOIN** est une jointure d'une table avec elle-même. Il est indispensable pour représenter des relations hiérarchiques ou réflexives (ex. : un employé a un manager, qui est lui-même un employé).

On utilise obligatoirement des **alias** pour distinguer les deux instances de la même table.
```

```{code-cell} python
conn.execute("""
ALTER TABLE employes ADD COLUMN manager_id INTEGER REFERENCES employes(emp_id)
""")
conn.executescript("""
UPDATE employes SET manager_id = NULL WHERE emp_id = 1;   -- Alice : DG
UPDATE employes SET manager_id = 1 WHERE emp_id IN (3,6); -- sous Alice
UPDATE employes SET manager_id = 2 WHERE emp_id = 5;      -- sous Bob
UPDATE employes SET manager_id = 1 WHERE emp_id = 4;      -- sous Alice
UPDATE employes SET manager_id = NULL WHERE emp_id = 2;   -- Bob : DG adjoint
UPDATE employes SET manager_id = NULL WHERE emp_id = 7;   -- Gilles sans manager
""")

df_self = pd.read_sql("""
SELECT
    e.prenom || ' ' || e.nom       AS employe,
    m.prenom || ' ' || m.nom       AS manager
FROM employes e
LEFT JOIN employes m ON e.manager_id = m.emp_id
ORDER BY manager, employe
""", conn)
print("SELF JOIN : employé → manager")
print(df_self.to_string(index=False))
```

---

## Jointures multiples (3+ tables)

```{prf:definition} Jointures multiples
:label: ch05-def-jointures-multiples
On peut enchaîner autant de jointures que nécessaire dans une requête. Chaque `JOIN` ajoute une table à l'ensemble intermédiaire. L'ordre des `JOIN` n'affecte pas le résultat logique (le moteur SQL optimise l'ordre d'exécution), mais peut influencer les performances.

La bonne pratique est de nommer toutes les tables avec des alias courts et de qualifier chaque colonne ambiguë par son alias de table.
```

```{code-cell} python
# 3 tables : employes + employe_projet + projets
df_3t = pd.read_sql("""
SELECT
    e.prenom || ' ' || e.nom  AS employe,
    p.nom                     AS projet,
    ep.role,
    p.budget
FROM employes e
INNER JOIN employe_projet ep ON e.emp_id    = ep.emp_id
INNER JOIN projets        p  ON ep.projet_id = p.projet_id
ORDER BY p.nom, e.nom
""", conn)
print("Jointure 3 tables : employes — projets — rôles")
print(df_3t.to_string(index=False))
```

```{code-cell} python
# 4 tables : employes + departements + employe_projet + projets
df_4t = pd.read_sql("""
SELECT
    e.prenom || ' ' || e.nom  AS employe,
    d.nom                     AS departement,
    p.nom                     AS projet,
    ep.role
FROM employes e
INNER JOIN departements   d  ON e.dept_id    = d.dept_id
INNER JOIN employe_projet ep ON e.emp_id     = ep.emp_id
INNER JOIN projets        p  ON ep.projet_id = p.projet_id
ORDER BY departement, employe
""", conn)
print("\nJointure 4 tables : employes — departements — projets — rôles")
print(df_4t.to_string(index=False))
```

---

## Pièges courants

```{prf:example} Pièges liés aux jointures
:label: ch05-ex-pieges
Trois pièges classiques à éviter :

**1. Doublons par jointure N-N** : joindre sans table de jonction sur une relation N-N multiplie les lignes. Toujours vérifier `COUNT(*)` avant et après une jointure.

**2. Colonnes ambiguës** : si `dept_id` existe dans `employes` et `departements`, écrire `SELECT dept_id` provoque une erreur d'ambiguïté. Toujours préfixer : `e.dept_id` ou `d.dept_id`.

**3. Jointure sur NULL** : `NULL = NULL` est `UNKNOWN`, jamais `TRUE`. Une jointure `ON e.dept_id = d.dept_id` n'inclura jamais les lignes où `dept_id` est `NULL` — utiliser un LEFT JOIN pour les inclure.
```

```{code-cell} python
# Démonstration : doublons si jointure N-N sans table de jonction
# Ici on voit qu'Alice apparaît deux fois car elle est sur 2 projets
df_doublons = pd.read_sql("""
SELECT e.prenom, COUNT(*) AS nb_lignes
FROM employes e
INNER JOIN employe_projet ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.prenom
HAVING COUNT(*) > 1
""", conn)
print("Employés apparaissant sur plusieurs projets (lignes dupliquées si non groupées) :")
print(df_doublons.to_string(index=False))
```

---

## Visualisation : diagrammes de Venn

```{code-cell} python
:tags: [hide-input]

fig, axes = plt.subplots(2, 2, figsize=(12, 10))
axes = axes.flatten()

join_types = [
    ("INNER JOIN",      "#1565c0", "#ef6c00", True,  True,  False),
    ("LEFT JOIN",       "#1565c0", "#ef6c00", True,  True,  True),
    ("RIGHT JOIN",      "#1565c0", "#ef6c00", True,  True,  False),
    ("FULL OUTER JOIN", "#1565c0", "#ef6c00", True,  True,  True),
]

titles = ["INNER JOIN", "LEFT JOIN", "RIGHT JOIN", "FULL OUTER JOIN"]
descriptions = [
    "Lignes communes aux deux tables",
    "Toutes les lignes de gauche\n+ correspondances à droite",
    "Toutes les lignes de droite\n+ correspondances à gauche",
    "Toutes les lignes des deux tables",
]

for ax, title, desc in zip(axes, titles, descriptions):
    ax.set_xlim(-1.5, 3.5)
    ax.set_ylim(-1.5, 1.5)
    ax.set_aspect("equal")
    ax.axis("off")

    is_inner = title == "INNER JOIN"
    is_left  = title in ("LEFT JOIN", "FULL OUTER JOIN")
    is_right = title in ("RIGHT JOIN", "FULL OUTER JOIN")

    # Cercle gauche
    left_alpha  = 0.55 if is_left  else 0.20
    right_alpha = 0.55 if is_right else 0.20
    inter_alpha = 0.75

    left_circle = plt.Circle((0.5, 0), 1.0, facecolor="#1565c0",
                              alpha=left_alpha, edgecolor="#1565c0", linewidth=2)
    right_circle = plt.Circle((1.5, 0), 1.0, facecolor="#ef6c00",
                               alpha=right_alpha, edgecolor="#ef6c00", linewidth=2)
    ax.add_patch(left_circle)
    ax.add_patch(right_circle)

    # Zone d'intersection (surbrillance)
    theta = np.linspace(-np.pi / 2, np.pi / 2, 200)
    # Hack : colorier la zone centrale avec un patch opaque si INNER ou FULL
    inter = plt.Circle((1.0, 0), 0.52, facecolor="#7b1fa2",
                        alpha=inter_alpha if (is_inner or is_left or is_right) else 0.0,
                        edgecolor="none")
    ax.add_patch(inter)

    ax.text(0.3, 0, "A", ha="center", va="center", fontsize=14,
            color="white", fontweight="bold")
    ax.text(1.7, 0, "B", ha="center", va="center", fontsize=14,
            color="white", fontweight="bold")

    ax.set_title(title, fontsize=13, fontweight="bold", pad=8)
    ax.text(1.0, -1.4, desc, ha="center", va="top", fontsize=9,
            color="#333", style="italic")

plt.suptitle("Types de jointures SQL — Diagrammes de Venn", fontsize=14, fontweight="bold", y=1.01)
plt.savefig("_static/ch05_venn_jointures.png", dpi=120, bbox_inches="tight")
plt.show()
```

```{code-cell} python
:tags: [hide-input]

# Comparaison visuelle du nombre de résultats par type de jointure
resultats = {}

resultats["INNER JOIN"] = pd.read_sql(
    "SELECT COUNT(*) AS n FROM employes e INNER JOIN departements d ON e.dept_id = d.dept_id", conn
).iloc[0]["n"]

resultats["LEFT JOIN"] = pd.read_sql(
    "SELECT COUNT(*) AS n FROM employes e LEFT JOIN departements d ON e.dept_id = d.dept_id", conn
).iloc[0]["n"]

resultats["CROSS JOIN"] = pd.read_sql(
    "SELECT COUNT(*) AS n FROM employes e CROSS JOIN departements d", conn
).iloc[0]["n"]

resultats["SELF JOIN"] = pd.read_sql(
    "SELECT COUNT(*) AS n FROM employes e LEFT JOIN employes m ON e.manager_id = m.emp_id", conn
).iloc[0]["n"]

fig, ax = plt.subplots(figsize=(8, 4))
noms = list(resultats.keys())
valeurs = list(resultats.values())
colors = sns.color_palette("muted", len(noms))
bars = ax.barh(noms, valeurs, color=colors, edgecolor="white", linewidth=1.2)
for bar, val in zip(bars, valeurs):
    ax.text(bar.get_width() + 0.3, bar.get_y() + bar.get_height() / 2,
            str(int(val)), va="center", fontsize=11, fontweight="bold")
ax.set_xlabel("Nombre de lignes retournées")
ax.set_title("Nombre de résultats selon le type de jointure\n(employes × departements)", fontweight="bold")
ax.set_xlim(0, max(valeurs) * 1.15)
plt.savefig("_static/ch05_resultats_jointures.png", dpi=120, bbox_inches="tight")
plt.show()
```

---

## Résumé

Ce chapitre a couvert l'ensemble des types de jointures SQL avec des exemples concrets :

1. **INNER JOIN** : lignes communes aux deux tables (le plus utilisé). Exclut les non-correspondances.
2. **LEFT JOIN** : toutes les lignes de gauche, `NULL` à droite si pas de correspondance. Permet l'anti-jointure.
3. **RIGHT JOIN** : symétrique du LEFT JOIN. Simulable avec un LEFT JOIN inversé.
4. **FULL OUTER JOIN** : toutes les lignes des deux côtés. Simulé en SQLite avec `UNION ALL`.
5. **CROSS JOIN** : produit cartésien. Puissant mais dangereux si mal maîtrisé.
6. **SELF JOIN** : jointure d'une table sur elle-même. Indispensable pour les hiérarchies.
7. **Jointures multiples** : enchaîner autant de `JOIN` que nécessaire, qualifier chaque colonne.

```{prf:remark}
:label: ch05-rem-performance
Du point de vue des performances, les jointures sur des colonnes indexées sont très rapides. L'optimiseur SQL choisit automatiquement le meilleur ordre de jointure. En cas de requête lente, les outils `EXPLAIN` (SQLite) ou `EXPLAIN ANALYZE` (PostgreSQL) révèlent le plan d'exécution choisi et les éventuels manques d'index.
```

```{prf:remark}
:label: ch05-rem-sql92
La syntaxe `JOIN … ON …` a été introduite dans SQL-92 (aussi appelé SQL2). L'ancienne syntaxe avec des virgules dans `FROM` et la condition dans `WHERE` (`FROM a, b WHERE a.id = b.id`) est fonctionnellement équivalente à un INNER JOIN mais moins lisible et plus propice aux oublis de condition — elle est à éviter dans le code moderne.
```