LATERAL JOIN et CROSS APPLY : exécuter une sous-requête par ligne en SQL

Dans la continuité de l'article JOINs spécialisés, on va faire un zoom sur le join LATERAL car à mon avis c'est probablement le plus sous-utilisé alors que c'est aussi un des plus puissants. C'est un join qui te permet de remplacer 30 lignes de SQL par 5 dans plein de cas concrets : top-N par groupe, dépliage de JSON, appels à des fonctions table paramétrées.

Le concept est simple car pour chaque ligne de la table externe, on exécute une sous-requête qui peut renvoyer plusieurs lignes et plusieurs colonnes. Ce que ne sait pas faire une sous-requête classique en SQL standard.

Le problème : la limite des sous-requêtes classiques

En SQL standard, une sous-requête dans un SELECT est limitée à une seule ligne et une seule colonne. Ça passe pour aller chercher un scalaire :

SELECT c.id, c.nom,
  (SELECT MAX(co.date_commande)
   FROM commandes co
   WHERE co.client_id = c.id) AS derniere_commande
FROM clients c;

Résultat :

Mais ça plante dès que tu veux ramener plusieurs colonnes ou plusieurs lignes :

SELECT c.id, c.nom,
  (SELECT *
   FROM commandes co
   WHERE co.client_id = c.id
   ORDER BY co.date_commande DESC
   LIMIT 3) AS top3
FROM clients c;
-- ERROR: subquery must return only one column

C'est exactement ce que LATERAL JOIN débloque.

La syntaxe

Trois type de syntaxe selon le moteur.

-- LATERAL (PostgreSQL, Snowflake, DuckDB)
SELECT c.id, c.nom, top3.*
FROM clients c
JOIN LATERAL (
  SELECT *
  FROM commandes co
  WHERE co.client_id = c.id
  ORDER BY co.date_commande DESC
  LIMIT 3
) AS top3 ON TRUE;

-- CROSS APPLY (SQL Server)
SELECT c.id, c.nom, top3.*
FROM clients c
CROSS APPLY (
  SELECT TOP 3 *
  FROM commandes co
  WHERE co.client_id = c.id
  ORDER BY co.date_commande DESC
) AS top3;

-- UNNEST style (BigQuery, mais pour les arrays uniquement)
SELECT u.id, tag
FROM users u, UNNEST(u.tags) AS tag;

Disponibilité :

JOIN LATERAL vs LEFT JOIN LATERAL : un piège qui coûte cher

Comme un JOIN classique, JOIN LATERAL est un INNER : si la sous-requête ne renvoie aucune ligne pour une ligne externe donnée, cette ligne externe disparaît du résultat.

Prenons un dataset concret. Trois clients, mais Chloé n'a aucune commande :

clients

commandes

Avec JOIN LATERAL, Chloé disparaît du résultat :

SELECT c.nom, top.date_commande, top.montant
FROM clients c
JOIN LATERAL (
  SELECT * FROM commandes co
  WHERE co.client_id = c.id
  ORDER BY co.date_commande DESC LIMIT 2
) AS top ON TRUE;

Avec LEFT JOIN LATERAL, Chloé est conservée (avec NULL) :

SELECT c.nom, top.date_commande, top.montant
FROM clients c
LEFT JOIN LATERAL (
  SELECT * FROM commandes co
  WHERE co.client_id = c.id
  ORDER BY co.date_commande DESC LIMIT 2
) AS top ON TRUE;

Côté SQL Server, c'est la même logique : CROSS APPLY est l'inner, OUTER APPLY est le left.

C'est typiquement le genre de piège qui passe les tests en dev (parce que tous les clients de test ont des commandes) et qui se voit en prod quand un client sans activité disparaît silencieusement d'un rapport.

Cas d'usage 1 : Top-N par groupe

Le cas par excellence. Pour chaque client, ramener ses 3 dernières commandes.

clients

commandes (5 pour Alice, 2 pour Bob)

SELECT c.nom, top3.date_commande, top3.montant
FROM clients c
JOIN LATERAL (
  SELECT date_commande, montant
  FROM commandes co
  WHERE co.client_id = c.id
  ORDER BY co.date_commande DESC
  LIMIT 3
) AS top3 ON TRUE;

Résultat :

Alice a ses 3 dernières commandes, Bob ses 2 (parce qu'il n'en a que 2 au total). Le LIMIT 3 s'applique par client, pas globalement.

L'équivalent avec une window function ROW_NUMBER :

WITH ranked AS (
  SELECT c.nom, co.date_commande, co.montant,
    ROW_NUMBER() OVER (
      PARTITION BY c.id
      ORDER BY co.date_commande DESC
    ) AS rn
  FROM clients c
  JOIN commandes co ON co.client_id = c.id
)
SELECT nom, date_commande, montant
FROM ranked
WHERE rn <= 3;

Pour lisibilité, LATERAL exprime simplement ("pour chaque client, prends ses 3 dernières commandes"). Côté performance, sur des partitions très grandes avec un petit N, LATERAL peut être plus rapide parce que le moteur peut s'arrêter dès qu'il a trouvé 3 lignes par groupe (early termination), alors que ROW_NUMBER doit calculer le rang de toutes les commandes avant de filtrer.

Sur Snowflake, BigQuery et Databricks, tu peux aussi utiliser QUALIFY qui rend l'écriture ROW_NUMBER plus compacte :

SELECT c.nom, co.date_commande, co.montant
FROM clients c
JOIN commandes co ON co.client_id = c.id
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY c.id
  ORDER BY co.date_commande DESC
) <= 3;

Cas d'usage 2 : Dépliage d'arrays et de JSON

En analytics engineering, les colonnes contenant des arrays ou du JSON imbriqué sont partout (logs d'événements, tags utilisateurs, attributs produits). LATERAL est l'outil pour les déplier en lignes exploitables.

Exemple concret. Une table users avec une colonne tags qui est un array :

-- Snowflake : LATERAL FLATTEN
SELECT u.user_id, t.value::STRING AS tag
FROM users u,
LATERAL FLATTEN(input => u.tags) t;

-- BigQuery : UNNEST
SELECT u.user_id, tag
FROM users u, UNNEST(u.tags) AS tag;

-- PostgreSQL : LATERAL avec unnest()
SELECT u.user_id, t.tag
FROM users u
JOIN LATERAL unnest(u.tags) AS t(tag) ON TRUE;

-- DuckDB : UNNEST direct
SELECT u.user_id, UNNEST(u.tags) AS tag
FROM users u;

Résultat (3 lignes en entrée, 6 en sortie) :

Une ligne par tag, avec le user_id répété pour conserver le lien. Tu peux ensuite joindre ce résultat à une table tag_definitions, faire des comptages par tag, ou alimenter une table de faits.

Avant LATERAL FLATTEN ou UNNEST, il fallait passer par des CTE récursives ou des JSON_EACH sur des bases plus anciennes. C'est probablement le cas d'usage qui justifie le plus l'adoption de Snowflake ou BigQuery sur des datasets riches en JSON.

Cas d'usage 3 : Fonction paramétrée qui retourne une table par ligne

Tu as une fonction qui retourne une table et pas une valeur, et tu veux l'appeler une fois par ligne externe avec des paramètres dynamiques.

-- PostgreSQL
CREATE FUNCTION get_user_permissions(user_id INT)
RETURNS TABLE (granted_at TIMESTAMP, role TEXT) AS $$
  SELECT granted_at, role
  FROM permissions
  WHERE permissions.user_id = $1;
$$ LANGUAGE SQL;

SELECT u.id, u.email, p.granted_at, p.role
FROM users u
JOIN LATERAL get_user_permissions(u.id) AS p ON TRUE;

Pour chaque ligne de users, on appelle get_user_permissions(u.id) qui peut retourner plusieurs lignes (une par permission). Sans LATERAL, tu ne pourrais pas passer u.id comme paramètre dynamique à une fonction table dans le FROM

Aller plus loin : pratiquer SQL

LATERAL JOIN, le top-N par groupe, le dépliage de JSON et l'interaction avec les window functions, c'est typiquement le genre de patterns qu'on rencontre tous les jours en analytics engineering. Pas juste à comprendre, mais à savoir écrire vite et juste, et à savoir reconnaître quand chaque approche est la meilleure.

Sur DataCertification.fr, les modules "JOINs", "Sous-requêtes et EXISTS" et "Requêtes avancées" couvrent ces patterns avec un éditeur SQL intégré et une validation automatique.

👉 Pratiquer ces patterns sur DataCertification.fr

Pour le panorama complet des joins avancés, voir l'article pillar JOINs spécialisés en SQL : SEMI, ANTI, ASOF, LATERAL et FULL OUTER.

Tu veux que je t'accompagne sur un projet data (modélisation, performance SQL, Snowflake, dbt, gouvernance) ? 👉 Réserver un appel de 30 minutes

Questions fréquentes