> ## Content Index
> Fetch the complete content index at: https://www.idriss-benbassou.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# LATERAL JOIN et CROSS APPLY : exécuter une sous-requête par ligne en SQL
- URL: https://www.idriss-benbassou.com/lateral-join-cross-apply-outer-apply-sql/
- Published: 2026-05-18T08:00:42.000Z
- Updated: 2026-06-24T07:32:51.000Z
- Author: Idriss BENBASSOU
- Tags: SQL, #rating-3

Dans la continuité de l'article [JOINs spécialisés](https://www.idriss-benbassou.com/joins-specialises-sql-semi-anti-asof-lateral-full-outer-join/), 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, par exemple :

```sql
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 :

| id | nom   | derniere\_commande |
| -- | ----- | ------------------ |
| 1  | Alice | 2026-05-15         |
| 2  | Bob   | 2026-05-10         |
| 3  | Chloé | NULL               |

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

```sql
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.

```sql
-- 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é :

| Moteur     | Syntaxe                                        |
| ---------- | ---------------------------------------------- |
| PostgreSQL | JOIN LATERAL                                   |
| Snowflake  | JOIN LATERAL (et LATERAL FLATTEN pour le JSON) |
| DuckDB     | JOIN LATERAL                                   |
| BigQuery   | UNNEST pour les arrays, JOIN corrélé sinon     |
| SQL Server | CROSS APPLY (inner) / OUTER APPLY (left)       |
| Databricks | JOIN LATERAL                                   |
| ClickHouse | Workaround via arrayJoin                       |

## 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`

| id | nom   |
| -- | ----- |
| 1  | Alice |
| 2  | Bob   |
| 3  | Chloé |

`commandes`

| id  | client\_id | date\_commande | montant |
| --- | ---------- | -------------- | ------- |
| 101 | 1          | 2026-05-15     | 120     |
| 102 | 1          | 2026-05-10     | 85      |
| 103 | 2          | 2026-05-10     | 60      |

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

```sql
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;
```

| nom   | date\_commande | montant |
| ----- | -------------- | ------- |
| Alice | 2026-05-15     | 120     |
| Alice | 2026-05-10     | 85      |
| Bob   | 2026-05-10     | 60      |

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

```sql
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;
```

| nom   | date\_commande | montant |
| ----- | -------------- | ------- |
| Alice | 2026-05-15     | 120     |
| Alice | 2026-05-10     | 85      |
| Bob   | 2026-05-10     | 60      |
| Chloé | NULL           | NULL    |

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`

| id | nom   |
| -- | ----- |
| 1  | Alice |
| 2  | Bob   |

`commandes` (5 pour Alice, 2 pour Bob)

| id  | client\_id | date\_commande | montant |
| --- | ---------- | -------------- | ------- |
| 101 | 1          | 2026-05-15     | 120     |
| 102 | 1          | 2026-05-10     | 85      |
| 103 | 1          | 2026-05-03     | 200     |
| 104 | 1          | 2026-04-20     | 50      |
| 105 | 1          | 2026-04-10     | 75      |
| 106 | 2          | 2026-05-12     | 60      |
| 107 | 2          | 2026-05-07     | 45      |

```sql
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 :

| nom   | date\_commande | montant |
| ----- | -------------- | ------- |
| Alice | 2026-05-15     | 120     |
| Alice | 2026-05-10     | 85      |
| Alice | 2026-05-03     | 200     |
| Bob   | 2026-05-12     | 60      |
| Bob   | 2026-05-07     | 45      |

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` :

```sql
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 :

```sql
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 :

| user\_id | tags                           |
| -------- | ------------------------------ |
| 1        | \['data', 'sql', 'snowflake'\] |
| 2        | \['python', 'ml'\]             |
| 3        | \['dbt'\]                      |

```sql
-- 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) :

| user\_id | tag       |
| -------- | --------- |
| 1        | data      |
| 1        | sql       |
| 1        | snowflake |
| 2        | python    |
| 2        | ml        |
| 3        | dbt       |

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.

```sql
-- 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](https://datacertification.fr/apprendre-sql?ref=idriss-benbassou.com)

Pour la vision complète des joins avancés, voir l'article pillar [JOINs spécialisés en SQL : SEMI, ANTI, ASOF, LATERAL et FULL OUTER](https://www.idriss-benbassou.com/joins-specialises-sql-semi-anti-asof-lateral-full-outer-join/).

Tu veux que je t'accompagne sur un projet data (modélisation, performance SQL, Snowflake, dbt, gouvernance) ? 👉 [Réserver un appel de 30 minutes](https://calendly.com/idriss-benbassou-datavio/30min?ref=idriss-benbassou.com)

## Questions fréquentes

#### Qu'est-ce qu'un LATERAL JOIN en SQL ?

Un `LATERAL JOIN` permet d'exécuter une sous-requête par ligne de la table externe, et de récupérer plusieurs lignes et plusieurs colonnes en retour. C'est pas possible avec une sous-requête classique. 

La syntaxe `JOIN LATERAL` existe notamment sur PostgreSQL, DuckDB, Snowflake et Databricks, avec de petites différences de syntaxe selon le moteur. Sur SQL Server, l’équivalent est `CROSS APPLY` pour le comportement inner et `OUTER APPLY` pour le comportement left. Sur BigQuery, on passe plutôt par `UNNEST` et des correlated joins, pas par un `JOIN LATERAL` générique.

#### Quelle différence entre JOIN LATERAL et LEFT JOIN LATERAL ?

Comme pour les joins classiques, `JOIN LATERAL` est un inner join donc si la sous-requête ne renvoie aucune ligne pour une ligne externe, cette ligne disparaît du résultat. `LEFT JOIN LATERAL` conserve toutes les lignes externes, même celles pour lesquelles la sous-requête est vide (les colonnes correspondantes seront `NULL`). Côté SQL Server, c'est `CROSS APPLY` (inner) vs `OUTER APPLY` (left).

#### LATERAL ou ROW\_NUMBER pour le top-N par groupe ?

Les deux marchent et donnent le même résultat. `LATERAL` est plus lisible. Côté performance, il peut être plus rapide quand N est petit (1 à 5) parce que le moteur peut s'arrêter dès qu'il a trouvé N lignes par groupe. `ROW_NUMBER` (souvent combiné avec `QUALIFY` sur Snowflake/BigQuery/Databricks) est préférable quand N est grand ou quand tu as besoin d'autres window functions dans la même requête.

#### Comment déplier un array ou un JSON en SQL ?

Sur Snowflake, utilise `LATERAL FLATTEN(input => colonne_json)`. Sur BigQuery, `UNNEST(colonne_array)` directement dans la clause `FROM`. Sur PostgreSQL, `JOIN LATERAL unnest(colonne_array) AS t(value) ON TRUE`. Sur DuckDB, `UNNEST(colonne_array)` directement dans le `SELECT`. Chacune de ces opérations crée une ligne par élément de l'array ou du JSON, avec les colonnes externes répétées.

#### CROSS APPLY et OUTER APPLY, c'est quoi la différence ?

Sur SQL Server, `CROSS APPLY` est l'équivalent de `JOIN LATERAL` donc un inner join qui exclut les lignes externes pour lesquelles la sous-requête ne renvoie rien. `OUTER APPLY` est l'équivalent de `LEFT JOIN LATERAL ` donc il conserve toutes les lignes externes, avec des `NULL` sur les colonnes de la sous-requête quand celle-ci est vide.

###