Introduction

Lorsque vous travaillez avec PostgreSQL dans de vrais projets backend, il est rare que vous n'interrogez qu'une seule table. Les données sont généralement normalisées en plusieurs tables liées entre elles par des clés étrangères, et vous devez les combiner pour obtenir des informations exploitables. Cette leçon se concentre sur trois techniques essentielles qui vous aideront à écrire des requêtes complexes de manière claire et efficace : JOIN pour relier les tables, subquery pour imbriquer des requêtes, et CTE pour structurer la logique d'interrogation.

Nous utiliserons un schéma exemple simulant un système de vente composé de quatre tables principales : customers, orders, products et order_items. La table customers stocke les informations clients, orders enregistre les commandes passées par les clients, products contient le catalogue produit, et order_items est la table intermédiaire qui stocke le détail de chaque produit dans chaque commande. C'est le type de relation un-à-plusieurs imbriquée que vous rencontrerez fréquemment.

JOIN entre plusieurs tables

JOIN est la manière la plus courante de combiner des données issues de plusieurs tables dans une même requête. PostgreSQL supporte INNER JOIN (ne renvoie que les enregistrements qui correspondent dans les deux tables), LEFT JOIN (renvoie tous les enregistrements de la table de gauche même s'il n'y a pas de correspondance à droite), ainsi que d'autres variantes comme RIGHT JOIN et FULL OUTER JOIN. En pratique backend, vous utiliserez surtout INNER JOIN et LEFT JOIN.

L'exemple ci-dessous récupère la liste des commandes avec le nom du client et le montant total :

SELECT o.id AS order_id, c.name AS customer_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

Lorsque vous devez joindre plusieurs tables en même temps, il suffit d'ajouter d'autres clauses JOIN. Par exemple, pour récupérer le détail de chaque produit d'une commande avec le nom du produit et la quantité :

SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;

Une erreur fréquente consiste à joindre des tables en oubliant la condition ON, ce qui produit un produit cartésien et ralentit considérablement la requête. Vérifiez systématiquement que chaque JOIN possède une condition explicite de correspondance de clé.

Sous-requêtes et sous-requêtes corrélées

Une subquery est une requête SELECT imbriquée à l'intérieur d'une autre requête. Vous pouvez utiliser une subquery dans une clause WHERE pour filtrer, dans SELECT pour calculer une valeur, ou dans FROM pour créer une table temporaire. Une forme particulière est la correlated subquery, dans laquelle la requête interne fait référence à une colonne de la requête externe, ce qui signifie qu'elle est ré-exécutée pour chaque ligne du résultat.

Exemple : trouver les clients ayant au moins une commande à l'aide d'une subquery simple :

SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 500);

Exemple de correlated subquery comptant le nombre de commandes pour chaque client affiché dans la liste :

SELECT c.name, c.email,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
WHERE c.created_at > '2024-01-01';

La requête interne fait référence à c.id depuis l'extérieur, donc PostgreSQL doit la ré-exécuter pour chaque client. Sur des tables volumineuses, la sous-requête corrélée peut être lente ; envisagez dans ce cas de la remplacer par un JOIN ou un CTE si la performance est un enjeu.

Expressions de table communes avec WITH

CTE (Common Table Expression) vous permet de définir des requêtes temporaires nommées directement dans l'instruction, ce qui rend une logique complexe plus lisible. Un CTE commence par le mot-clé WITH, suivi d'une liste de requêtes nommées, puis de la requête principale qui les utilise.

Exemple : calculer le chiffre d'affaires total par client pour l'année 2024, puis filtrer ceux dont le chiffre d'affaires dépasse 1000 :

WITH customer_revenue AS (
  SELECT o.customer_id, SUM(o.total_amount) AS revenue
  FROM orders o
  WHERE o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
  GROUP BY o.customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
INNER JOIN customers c ON c.id = cr.customer_id
WHERE cr.revenue > 1000
ORDER BY cr.revenue DESC;

CTE supporte également la forme récursive (recursive CTE) avec le mot-clé WITH RECURSIVE, très utile pour parcourir des hiérarchies comme l'organigramme d'une entreprise ou des catégories produits à plusieurs niveaux. Par ailleurs, PostgreSQL 12 et supérieur permet la matérialisation avec WITH ... AS MATERIALIZED pour accélérer la requête dans certains cas.

Erreurs courantes et astuces de terrain

Une erreur fréquente consiste à confondre JOIN et subquery dans une clause WHERE utilisant IN ou EXISTS. Les deux donnent des résultats équivalents dans de nombreuses situations, mais EXISTS est souvent plus rapide lorsque vous avez seulement besoin de vérifier l'existence au lieu de récupérer une valeur. Exemple : trouver les clients ayant déjà passé commande :

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Lorsque vous écrivez un CTE, donnez-lui des noms explicites comme customer_revenue ou top_products pour que le lecteur comprenne l'intention de chaque étape. Évitez l'usage excessif des CTE quand un simple JOIN suffit, car les CTE peuvent entraîner un coût d'optimisation supplémentaire dans certaines versions de PostgreSQL. Enfin, examinez toujours le plan d'exécution avec EXPLAIN ANALYZE lorsqu'une requête complexe est lente, car une sous-requête corrélée peut parfois être avantageusement remplacée par un JOIN.

Synthèse

Dans cette leçon, vous avez appris à combiner plusieurs tables avec JOIN, à écrire des subquery et des correlated subquery pour filtrer et calculer, ainsi qu'à utiliser un CTE avec WITH pour structurer des requêtes complexes en blocs lisibles. Ces trois techniques constituent le socle qui vous permettra de traiter la majorité des besoins d'interrogation dans une application backend réelle, de la récupération de listes de commandes avec leurs détails au calcul de rapports agrégés. Dans la prochaine leçon, vous approfondirez l'indexation et l'optimisation des performances pour les requêtes complexes que vous venez d'écrire.