JOINs spécialisés en SQL : SEMI, ANTI, ASOF, LATERAL et FULL OUTER

Tout le monde connaît INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL JOIN. Sauf que dès que tu travailles sur des cas un peu spécial (top-N par groupe, alignement de séries temporelles, déduplication propre des données, comparaison de versions de tables), ces joins classiques ne suffisent plus.

Les bases de données modernes (Snowflake, BigQuery, Databricks, DuckDB, PostgreSQL, ClickHouse etc....) proposent 4 joins spécialisés qui répondent chacun à un besoin précis. Soit en syntaxe native, soit en équivalent SQL standard que l'optimiseur reconnaît et exécute comme un join physique spécial.

Donc l'objectif de cet article est de savoir à quoi ils servent, comment les écrire, sur quel moteur ils sont disponibles.

Pourquoi les joins de base ne suffisent plus

Voici quatre cas très concrets que les jointures classiques ne savent pas traiter facilement.

Quand utiliser les jointures spéciales ?

Les joins

SEMI JOIN

Un Semi join est un INNER JOIN qui filtre la table de gauche en fonction de la table de droite, mais qui ne ramène aucune colonne de la droite et ne duplique en aucun cas les lignes de gauche.

-- Syntaxe native (Databricks, DuckDB, Spark, ClickHouse)
SELECT *
FROM clients c
LEFT SEMI JOIN commandes co
  ON co.client_id = c.id;

-- Équivalent universel (Snowflake, BigQuery, Postgres, SQL Server)
SELECT *
FROM clients c
WHERE EXISTS (
  SELECT 1 FROM commandes co
  WHERE co.client_id = c.id
);

Cas d'usage : "tous les clients qui ont commandé au moins une fois". Avec un INNER JOIN classique, un client avec 50 commandes apparaîtrait 50 fois. Le SEMI JOIN, lui, le renvoie une seule fois. Les optimiseurs modernes reconnaissent EXISTS et l'exécutent en interne comme un semi-join physique.

ANTI JOIN

L'exact opposé du SEMI JOIN. Garde uniquement les lignes de la table de gauche qui n'ont aucune correspondance dans la table de droite.

-- Syntaxe native (Databricks, DuckDB, Spark)
SELECT *
FROM clients c
LEFT ANTI JOIN commandes co
  ON co.client_id = c.id;

-- Équivalent 
SELECT *
FROM clients c
WHERE NOT EXISTS (
  SELECT 1 FROM commandes co
  WHERE co.client_id = c.id
);

Cas d'usage : "clients qui n'ont jamais passé de commande", "lignes orphelines après une jointure", "valeurs présentes dans la source mais pas dans la cible après ETL".

À noter, ne pas confondre avec NOT IN qui se comporte mal en présence de NULL. J'ai détaillé ce piège dans NOT IN vs NOT EXISTS en SQL : pourquoi le résultat diffère.

LATERAL JOIN et CROSS APPLY

Le join qui débloque les sous-requêtes corrélées renvoyant plusieurs lignes. En SQL standard, une sous-requête dans le SELECT ne peut renvoyer qu'une seule ligne et une seule colonne. LATERAL (ou CROSS APPLY côté SQL Server) lève cette limite.

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

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

Cas d'usage : top-N par groupe (les 3 dernières commandes par client, les 5 produits les plus vendus par catégorie), dépliage d'un array ou de JSON (LATERAL FLATTEN en Snowflake, UNNEST en BigQuery) ou appels à des fonctions table paramétrées.

C’est probablement la jointure la plus sous-utilisée de la liste, alors qu’elle peut remplacer, dans certains cas, 30 lignes de SQL par 5.

ASOF JOIN

Le join des séries temporelles. Pour chaque ligne de la table de gauche, il joint la ligne de la table de droite dont le timestamp est le plus proche (juste avant, juste après, ou égal selon l'opérateur) ce qui est un cauchemar à faire avec une jointure normale

-- Syntaxe Snowflake
SELECT t.timestamp_trade, t.price, q.bid, q.ask
FROM trades t
ASOF JOIN quotes q
  MATCH_CONDITION (t.timestamp_trade >= q.timestamp_quote)
  ON t.symbol = q.symbol;

-- Syntaxe DuckDB
SELECT t.timestamp_trade, t.price, q.bid, q.ask
FROM trades t
ASOF JOIN quotes q
  ON t.symbol = q.symbol
  AND t.timestamp_trade >= q.timestamp_quote;

Cas d'usage : finance (enrichir des transactions avec le prix de marché au moment exact), web analytics et CRM (attribuer un événement utilisateur à la session active la plus récente) etc.....

Sans ASOF JOIN, il faut passer par une ROW_NUMBER + sous-requête, ce qui est lourd et lent. Snowflake annonce 12 à 16 fois plus rapide avec la syntaxe native par rapport au workaround classique.

FULL OUTER JOIN

Ce join n'est pas spécial et n’a rien de nouveau car il existe dans le SQL standard depuis longtemps, mais il est rarement utilisé. Et si tu viens de l’univers de Qlik, cela te sera familier. Il garde toutes les lignes des deux tables, qu’elles aient une correspondance ou non. Quand il n’y a pas de match d’un côté, les colonnes correspondantes sont NULL.

SELECT
  COALESCE(a.id, b.id)        AS id,
  a.value                     AS valeur_avant,
  b.value                     AS valeur_apres,
  CASE
    WHEN a.id IS NULL THEN 'ajouté'
    WHEN b.id IS NULL THEN 'supprimé'
    WHEN a.value <> b.value THEN 'modifié'
    ELSE 'inchangé'
  END AS type_modif
FROM version_avant a
FULL OUTER JOIN version_apres b
  ON a.id = b.id;

Disponibilité par moteur

Tous les moteurs SQL ne supportent pas les cinq joins en syntaxe native. Voici l'état des lieux en 2026 :

Points à retenir :

• Pour SEMI et ANTI sans syntaxe native, l'optimiseur reconnaît EXISTS / NOT EXISTS et les exécute en interne comme des semi-joins / anti-joins physiques. Donc tu obtiens la performance d'un join spécialisé même sans le mot-clé.
• ASOF JOIN est le plus récent. Snowflake l'a livré en GA en mai 2024, DuckDB et ClickHouse le supportent depuis plus longtemps. Sur les autres moteurs, il faut faire avec une ROW_NUMBER + sous-requête.
• LATERAL est très largement disponible mais sous des noms différents selon le moteur.

Aller plus loin : pratiquer SQL

Les joins spécialisés, comme les autres patterns SQL avancés, s'apprennent en pratiquant. Lire un article ne suffit pas, il faut écrire les requêtes, voir les résultats, se prendre les cas piégeux en face.

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é au navigateur et une validation automatique des résultats.

👉 Pratiquer ces joins sur DataCertification.fr

Si tu travailles déjà sur des cas plus complexes (pipelines, modélisation dimensionnelle, qualité de données, déduplication, SCD, incrémental), le Lab Analytics Engineering applique tous ces patterns à des workflows complets.

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