Jointures LATERAL PostgreSQL : exemples CROSS et LEFT

Une sous-requête LATERAL dans PostgreSQL peut utiliser les valeurs d’un élément précédent de la clause FROM. Elle est utile lorsque les lignes à joindre dépendent de la ligne source courante, par exemple pour sélectionner les deux dernières commandes payées de chaque client.

Utilisez CROSS JOIN LATERAL si une ligne source doit disparaître lorsque la sous-requête ne renvoie rien. Utilisez LEFT JOIN LATERAL ... ON TRUE si elle doit rester avec des valeurs NULL à droite. Le résultat réel de la sous-requête compte : un agrégat peut renvoyer une ligne même lorsque son entrée est vide.

Ce guide exécute les deux formes sur des données originales de clients et de commandes. Les 29 tests passent avec PostgreSQL 18.6 : horodatages identiques, correspondances absentes, position des filtres, fonctions de fenêtre et agrégats. Ces petits tests de justesse ne démontrent pas que LATERAL est plus rapide qu’une autre requête.

Préparer des données avec égalités et correspondances absentes

Utilisez une base jetable nommée lateral_demo, où lateral_lab n’existe pas encore. Votre rôle doit pouvoir créer le schéma et les tables. Enregistrez les blocs numérotés dans les fichiers indiqués et exécutez-les dans l’ordre. Par exemple : psql -X -qAt -P 'null=<NULL>' -v ON_ERROR_STOP=1 -d lateral_demo -f 01-setup.sql. Ajoutez vos options de connexion.

Les sorties utilisent le format non aligné de psql, sans en-têtes, avec des barres verticales entre les colonnes et <NULL> pour SQL NULL. Notre guide PostgreSQL avec Docker Compose, en anglais, décrit un environnement d’apprentissage distinct dans un conteneur.

Exécutez 01-setup.sql :

CREATE SCHEMA lateral_lab;
CREATE TABLE lateral_lab.customers (
    customer_id integer PRIMARY KEY,
    name text NOT NULL
);
CREATE TABLE lateral_lab.orders (
    order_id integer PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES lateral_lab.customers,
    placed_at timestamp NOT NULL,
    status text NOT NULL CHECK (status IN ('paid', 'cancelled')),
    amount numeric(10,2) NOT NULL
);
INSERT INTO lateral_lab.customers VALUES
    (1, 'Alpha'), (2, 'Beta'), (3, 'Gamma'), (4, 'Delta');
INSERT INTO lateral_lab.orders VALUES
    (101, 1, '2026-01-01 09:00', 'paid', 10),
    (102, 1, '2026-01-02 09:00', 'paid', 20),
    (103, 1, '2026-01-02 09:00', 'paid', 30),
    (104, 1, '2026-01-03 09:00', 'cancelled', 90),
    (201, 2, '2026-01-02 10:00', 'paid', 40),
    (202, 2, '2026-01-01 10:00', 'cancelled', 80),
    (401, 4, '2026-01-02 11:00', 'cancelled', 70);
SELECT count(*) FROM lateral_lab.customers;
SELECT count(*), sum(amount) FROM lateral_lab.orders;

4
7|340.00

Alpha possède trois commandes payées, dont deux au même instant, et une commande annulée plus récente. Beta possède une commande payée et une commande annulée plus ancienne. Gamma n’a aucune commande. Delta a une commande, mais aucune avec le statut paid. La différence entre absence de lignes et absence de lignes admissibles sera visible dans les résultats.

Les horodatages sont des valeurs locales fictives sans fuseau horaire. Ils sont NOT NULL : ces exemples de classement n’ont donc pas besoin d’une règle supplémentaire pour les horodatages NULL.

Renvoyer les deux dernières commandes payées par client

Exécutez 02-cross.sql :

SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM lateral_lab.customers AS c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.placed_at, o.amount
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 2
) AS recent
ORDER BY c.customer_id, recent.placed_at DESC, recent.order_id DESC;

1|Alpha|103|30.00
1|Alpha|102|20.00
2|Beta|201|40.00

La condition o.customer_id = c.customer_id corrèle la sous-requête au client courant. Le filtrage, le tri et LIMIT 2 s’appliquent aux commandes admissibles de ce client. Le résultat complet contient trois lignes : deux pour Alpha et une pour Beta. La limite ne porte pas sur deux lignes pour tout le rapport.

Les commandes 102 et 103 ont le même horodatage. Le tri secondaire sur order_id, unique, place 103 en premier. L’ORDER BY interne détermine les lignes retenues par la limite ; l’ORDER BY externe définit l’ordre final du rapport. La documentation de LIMIT explique pourquoi un sous-ensemble prévisible exige un ordre complet.

Gamma et Delta disparaissent parce que la sous-requête ne renvoie aucune ligne admissible pour eux. Notre requête équivalente INNER JOIN LATERAL ... ON TRUE produit le même résultat. Choisissez ce comportement seulement si les clients sans commandes correspondantes doivent être absents.

La référence LATERAL décrit les colonnes précédentes accessibles. Supprimer LATERAL tout en conservant cette référence externe provoque le SQLSTATE 42P01 dans notre test. Supprimer plutôt la condition de corrélation répète les deux dernières commandes payées globales pour chaque client : le rapport est différent.

Conserver les clients sans commande admissible

Exécutez 03-left.sql :

SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM lateral_lab.customers AS c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.placed_at, o.amount
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 2
) AS recent ON TRUE
ORDER BY c.customer_id, recent.placed_at DESC, recent.order_id DESC;

1|Alpha|103|30.00
1|Alpha|102|20.00
2|Beta|201|40.00
3|Gamma|<NULL>|<NULL>
4|Delta|<NULL>|<NULL>

LEFT JOIN ajoute une ligne complétée par NULL pour Gamma et une pour Delta. ON TRUE n’ajoute aucune restriction de jointure, car la relation au client et le filtre paid sont déjà dans la sous-requête.

Le résultat contient cinq lignes pour quatre clients. Une jointure latérale gauche ne garantit pas une seule ligne par ligne source : Alpha contribue toujours deux correspondances. Pour une seule commande récente par client, utilisez LIMIT 1 avec un ordre déterministe. Pour un résumé unique par client, choisissez un agrégat adapté.

Placer les filtres avant ou après la limite selon le besoin

Le fichier 04-filter-after.sql sélectionne d’abord la dernière commande de chaque client, quel que soit son statut, puis filtre le résultat joint :

SELECT c.customer_id, c.name, recent.order_id
FROM lateral_lab.customers AS c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.placed_at, o.status
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 1
) AS recent ON TRUE
WHERE recent.status = 'paid'
ORDER BY c.customer_id;

2|Beta|201

Seul Beta reste. La dernière commande d’Alpha est annulée : le filtre externe supprime donc Alpha, malgré ses commandes payées. Le statut à droite de Gamma est NULL, ce qui ne satisfait pas non plus status = 'paid'. La commande annulée de Delta échoue aussi au filtre.

Pour obtenir la dernière commande payée tout en conservant tous les clients, appliquez la condition paid dans la sous-requête, avant LIMIT. Exécutez 05-filter-before.sql :

SELECT c.customer_id, c.name, recent.order_id
FROM lateral_lab.customers AS c
LEFT JOIN LATERAL (
    SELECT o.order_id
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 1
) AS recent ON TRUE
ORDER BY c.customer_id;

1|Alpha|103
2|Beta|201
3|Gamma|<NULL>
4|Delta|<NULL>

Alpha renvoie maintenant la commande 103, Beta la 201, et Gamma et Delta restent avec un identifiant de commande NULL. Ces requêtes répondent à des questions différentes. Déplacer la condition paid dans ON après avoir sélectionné la dernière commande globale conserverait un client sans correspondance, mais ne rechercherait toujours pas une commande payée antérieure à celle annulée.

Comparer une solution avec fonction de fenêtre

Un rapport des N premières lignes par groupe peut aussi classer d’abord les commandes admissibles. Exécutez 06-window.sql :

WITH ranked AS (
    SELECT o.*,
           row_number() OVER (
               PARTITION BY o.customer_id
               ORDER BY o.placed_at DESC, o.order_id DESC
           ) AS position
    FROM lateral_lab.orders AS o
    WHERE o.status = 'paid'
)
SELECT c.customer_id, c.name, r.order_id, r.amount
FROM lateral_lab.customers AS c
LEFT JOIN ranked AS r
    ON r.customer_id = c.customer_id AND r.position <= 2
ORDER BY c.customer_id, r.placed_at DESC, r.order_id DESC;

1|Alpha|103|30.00
1|Alpha|102|20.00
2|Beta|201|40.00
3|Gamma|<NULL>|<NULL>
4|Delta|<NULL>|<NULL>

Cette sortie correspond exactement au résultat LEFT JOIN LATERAL limité à deux commandes. Le prédicat r.position <= 2 reste ici dans la condition de jointure pour conserver les clients sans commandes classées. Le déplacer dans un WHERE externe supprime Gamma et Delta dans notre test.

Le tutoriel PostgreSQL sur les fonctions de fenêtre présente row_number et le classement par groupe. Nos comparaisons correspondent aussi pour les limites zéro, un et trois. Elles vérifient le sens du résultat, sans établir une préférence universelle pour un plan.

Choisissez la forme qui exprime clairement la tâche, puis examinez son plan avec des données et paramètres réalistes. LATERAL est pratique lorsqu’une ligne source fournit les entrées d’une recherche limitée. Une fonction de fenêtre peut convenir lorsque vous avez déjà besoin de classements sur un ensemble plus large. Aucune écriture ne garantit un temps d’exécution inférieur.

Tenir compte des agrégats qui renvoient une ligne sur entrée vide

Exécutez 07-aggregate.sql :

SELECT c.customer_id, totals.paid_count, totals.paid_amount
FROM lateral_lab.customers AS c
CROSS JOIN LATERAL (
    SELECT count(*) AS paid_count, sum(o.amount) AS paid_amount
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
) AS totals
ORDER BY c.customer_id;

1|3|60.00
2|1|40.00
3|0|<NULL>
4|0|<NULL>

Les quatre clients restent malgré CROSS JOIN. La sous-requête agrégée n’a pas de GROUP BY : elle produit une ligne par invocation. count(*) vaut zéro et sum(amount) vaut NULL sans commande admissible. La référence des agrégats documente ces résultats sur entrée vide.

Notre test distinct ajoute GROUP BY o.customer_id dans cette sous-requête. Les groupes vides ne produisent alors aucune ligne, et CROSS JOIN supprime Gamma et Delta. Examinez ce que renvoie la sous-requête au lieu de supposer qu’une absence de correspondance dans la table implique toujours un résultat vide.

Si les règles du rapport assimilent un total absent à zéro, appliquez COALESCE explicitement. Conservez le sens de NULL lorsque l’absence doit rester distincte d’un total réel égal à zéro.

Envisager un index, puis examiner le plan réel

Pour cette recherche de commandes payées, 08-index.sql crée un index partiel candidat aligné sur le client et l’ordre de classement :

CREATE INDEX orders_paid_recent_idx
ON lateral_lab.orders (customer_id, placed_at DESC, order_id DESC)
WHERE status = 'paid';
ANALYZE lateral_lab.orders;
ANALYZE lateral_lab.customers;

L’index contient uniquement les commandes payées et ordonne les entrées par client, horodatage décroissant et identifiant décroissant. Notre test vérifie sa définition et confirme que la jointure gauche produit le même résultat après création.

La documentation des index partiels explique les conditions liées au prédicat. Cet index ne couvre pas de la même façon la recherche de la dernière commande tous statuts confondus ; un prédicat paramétré demande aussi son propre examen de plan. Ne supposez pas qu’un index sera choisi simplement parce qu’il existe.

Nous avons capturé un plan JSON sans imposer l’index, mais sans mesurer les performances de ces sept lignes. Notre guide PostgreSQL EXPLAIN ANALYZE, en anglais, aide à examiner une charge représentative. Comparez le nombre de clients sélectionnés, les commandes par client, les filtres, les boucles et les lignes réelles avant de conclure sur les performances.

Supprimer les objets de l’exercice

Après l’exercice, exécutez 09-cleanup.sql uniquement dans la base jetable :

DROP TABLE lateral_lab.orders;
DROP TABLE lateral_lab.customers;
DROP SCHEMA lateral_lab;

La table orders est supprimée avant customers à cause de la clé étrangère ; son index disparaît avec elle. Notre programme confirme le nettoyage, arrête son serveur isolé et supprime le fichier de mot de passe temporaire.

L’archive de revue contient les scripts SQL, les sorties capturées, le plan et les vérifications. Avant d’adapter une requête LATERAL, décidez si les lignes sources sans correspondance doivent rester, comment départager les égalités et quels prédicats doivent s’appliquer avant la limite par ligne.