Jointures SQL expliquees

Jointures SQL Expliquées

Pourquoi Jointures SQL expliquées ?

• Contexte réel : Un développeur SQL utilise régulièrement des jointures pour combiner et analyser des données provenant de différentes tables.
• Un cas d'utilisation concret : Vous voulez afficher tous les projets associés à un utilisateur en utilisant son identifiant.

Prerequis

• Connaissances nécessaires :
  • Comprendre la structure SQL des bases de données
  • Savoir créer et manipuler des tables
  • Comprendre les concepts de clés primaires et étrangères
• Outils à installer :
  • Un client SQL comme pgAdmin, DBeaver ou MySQL Workbench
  • Une base de données relationnelle comme PostgreSQL, MySQL ou SQLite

Concepts fondamentaux

1. Jointure INTERNE (INNER JOIN)

La jointure interne retourne uniquement les lignes où la condition spécifiée est vraie.

-- Schema mental : On prend tous les employés qui ont un département valide
SELECT e.id, e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

2. Jointure EXTERNE GAUCHE (LEFT JOIN)

La jointure externe gauche retourne toutes les lignes de la table de gauche, ainsi que les lignes correspondantes de la table de droite si elles existent.

-- Schema mental : On prend tous les employés et leurs départements s'ils en ont
SELECT e.id, e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

3. Jointure EXTERNE DROITE (RIGHT JOIN)

La jointure externe droite retourne toutes les lignes de la table de droite, ainsi que les lignes correspondantes de la table de gauche si elles existent.

-- Schema mental : On prend tous les départements et leurs employés s'ils en ont
SELECT e.id, e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

4. Jointure FULL OUTER (FULL JOIN)

La jointure full outer retourne toutes les lignes des deux tables, y compris les lignes sans correspondance.

-- Schema mental : On prend tous les employés et leurs départements, même ceux qui n'ont pas de département associé
SELECT e.id, e.name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;

5. Jointure CROisée (CROSS JOIN)

La jointure croisée retourne toutes les combinaisons possibles des lignes de deux tables.

-- Schema mental : On prend toutes les combinaisons d'employés et de départements
SELECT e.id, e.name, d.department_name
FROM employees e
CROSS JOIN departments d;

Mise en pratique : projet fil rouge

Mini-projet : Gestionnaire de Tâches

Étape 1 : Création des tables

-- Création de la table 'users'
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- Création de la table 'tasks'
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    user_id INT REFERENCES users(id)
);

Étape 2 : Insertion des données

-- Ajout d'utilisateurs
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
INSERT INTO users (username, email) VALUES ('jane_smith', 'jane@example.com');

-- Ajout de tâches
INSERT INTO tasks (title, description, user_id) VALUES ('Task 1', 'Description for Task 1', 1);
INSERT INTO tasks (title, description, user_id) VALUES ('Task 2', 'Description for Task 2', 1);
INSERT INTO tasks (title, description, user_id) VALUES ('Task 3', 'Description for Task 3', 2);

Étape 3 : Jointure pour récupérer les tâches d'un utilisateur

-- Récupération des tâches de l'utilisateur 'john_doe'
SELECT t.id, t.title, t.description
FROM tasks t
INNER JOIN users u ON t.user_id = u.id
WHERE u.username = 'john_doe';

Étape 4 : Ajout d'une tâche sans utilisateur

-- Ajout d'une tâche sans utilisateur
INSERT INTO tasks (title, description) VALUES ('Task 4', 'Description for Task 4');

Étape 5 : Jointure externe gauche pour récupérer toutes les tâches et leurs utilisateurs

-- Récupération de toutes les tâches et leurs utilisateurs s'ils en ont
SELECT t.id, t.title, t.description, u.username
FROM tasks t
LEFT JOIN users u ON t.user_id = u.id;

Structure des fichiers

• schema.sql : Contient les instructions SQL pour la création des tables.
• data.sql : Contient les instructions SQL pour l'insertion des données.
• queries.sql : Contient les instructions SQL pour les jointures et les requêtes.

Erreurs frequentes et debugging

1. ERREUR : "ERROR: column 'user_id' does not exist"

-- Mauvais
SELECT t.id, t.title, u.description
FROM tasks t
INNER JOIN users u ON t.user_id = u.id;

-- Correct
SELECT t.id, t.title, t.description
FROM tasks t
INNER JOIN users u ON t.user_id = u.id;

2. ERREUR : "ERROR: null value in column 'user_id' violates not-null constraint"

-- Mauvais
INSERT INTO tasks (title, description) VALUES ('Task 5', NULL);

-- Correct
INSERT INTO tasks (title, description) VALUES ('Task 5', 'Description for Task 5');

3. ERREUR : "ERROR: operator does not exist: integer = text"

-- Mauvais
SELECT t.id, t.title, t.description
FROM tasks t
INNER JOIN users u ON t.user_id = u.username;

-- Correct
SELECT t.id, t.title, t.description
FROM tasks t
INNER JOIN users u ON t.user_id = u.id;

Pour aller plus loin

1. Jointures multi-tables

• Explorer les jointures entre plusieurs tables pour analyser des données complexes.
• Documentation PostgreSQL: Multi-table Joins

2. Optimisation des jointures

• Utiliser les index pour accélérer les jointures.
• Documentation MySQL: Index

3. Jointures avec conditions spécifiques

• Utiliser des conditions dans les jointures pour filtrer les résultats.
• Documentation SQL Server: JOIN Conditions

Défi pratique : Créer un scraper de données

• Créer un script Python qui utilise SQL pour extraire et analyser des données d'une source externe.
• Documentation SQLAlchemy: ORM