Leçon 5.5 · Temps de lecture : ~6 min

Le SQL FULL OUTER JOIN renvoie toutes les lignes des deux tables : les lignes correspondantes sont combinées, et les lignes sans correspondance d'un côté comme de l'autre sont conservées avec NULL dans les colonnes de l'autre table. Dans cette leçon, vous apprendrez la syntaxe, quels SGBD la prennent en charge et comment obtenir le même résultat dans MySQL et MariaDB.

SQL FULL OUTER JOIN : garder toutes les lignes des deux tables

LEFT JOIN conserve les lignes sans correspondance d'une table, RIGHT JOIN celles de l'autre. FULL OUTER JOIN conserve les deux. C'est l'outil pour comparer deux listes : ce qui est dans les deux, ce qui n'est que dans la première et ce qui n'est que dans la seconde.

Ce que renvoie FULL OUTER JOIN

Le résultat se compose de trois parties :

1. Les paires de lignes correspondantes, comme avec INNER JOIN.
2. Les lignes de la table de gauche sans correspondance, avec NULL dans les colonnes de droite.
3. Les lignes de la table de droite sans correspondance, avec NULL dans les colonnes de gauche.

Syntaxe de base

SELECT left_table.column1, right_table.column2
FROM left_table
FULL OUTER JOIN right_table ON left_table.key_column = right_table.key_column;

FULL JOIN et FULL OUTER JOIN sont identiques.

Exemple : acteurs et clients

Qui, dans la base Sakila, est acteur, qui est client, et qui est les deux ? Dans PostgreSQL ou SQL Server, une jointure complète sur le nom répond directement :

-- PostgreSQL, SQL Server
SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name;

Chaque acteur apparaît une fois, chaque client une fois, et une personne qui est les deux apparaît sur une seule ligne avec les deux côtés remplis.

FULL OUTER JOIN dans MySQL et MariaDB

MySQL et MariaDB n'ont pas de FULL OUTER JOIN, mais on peut obtenir le même résultat avec deux requêtes réunies par UNION ALL :

1. Un LEFT JOIN renvoie les correspondances et les lignes présentes seulement dans la table de gauche.
2. Un RIGHT JOIN avec WHERE left_key IS NULL ajoute uniquement les lignes présentes seulement dans la table de droite.

SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
LEFT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
UNION ALL
SELECT a.first_name, a.last_name, c.first_name, c.last_name
FROM actor AS a
RIGHT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL;

Résultat : 798 lignes : 200 acteurs plus 599 clients, moins une personne qui est les deux et apparaît sur une seule ligne combinée (JENNIFER DAVIS).

On voit parfois cette solution écrite avec UNION au lieu de UNION ALL et sans condition WHERE. Elle ne fonctionne que si le résultat ne contient pas de lignes légitimement répétées, car UNION supprime tous les doublons, et elle est plus lente. La version avec UNION ALL est exacte. Vous apprendrez UNION et UNION ALL dans la leçon 5.10.

Utiliser une jointure complète pour comparer des listes

Une jointure complète est utile chaque fois que deux sources doivent contenir les mêmes éléments :

• rapprocher les paiements de la base de données d'un relevé bancaire ;
• comparer un catalogue de produits avec l'état du stock ;
• trouver les enregistrements présents dans un seul des deux systèmes après une migration.

Pour ne garder que les écarts, filtrez les lignes où l'un des côtés vaut NULL :

-- PostgreSQL, SQL Server : les personnes seulement acteurs ou seulement clients
SELECT a.actor_id, c.customer_id
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL OR c.customer_id IS NULL;

Points clés :

• FULL OUTER JOIN renvoie les lignes correspondantes plus les lignes sans correspondance des deux tables.
• Les colonnes du côté manquant valent NULL.
• PostgreSQL, SQL Server, Oracle et SQLite 3.39+ le prennent en charge ; MySQL et MariaDB non.
• Dans MySQL et MariaDB : LEFT JOIN UNION ALL RIGHT JOIN ... WHERE left_key IS NULL.
• Une jointure complète est l'outil naturel pour comparer deux listes.

Questions fréquentes

MySQL prend-il en charge FULL OUTER JOIN ?

Non. MySQL et MariaDB ne le prennent pas en charge. Combinez un LEFT JOIN et un RIGHT JOIN avec UNION ALL, en ne gardant que les lignes sans correspondance dans la seconde requête (WHERE left_key IS NULL).

Quelle est la différence entre FULL OUTER JOIN et CROSS JOIN ?

FULL OUTER JOIN associe les lignes selon une condition et conserve les lignes sans correspondance des deux tables. CROSS JOIN n'a pas de condition et renvoie toutes les combinaisons de lignes, son résultat est donc généralement bien plus grand.

Pourquoi utiliser UNION ALL plutôt qu'UNION pour émuler FULL OUTER JOIN ?

UNION supprime les lignes en double, y compris celles qui sont légitimement répétées dans les données, et demande un travail supplémentaire pour trouver les doublons. UNION ALL avec le filtre IS NULL dans la seconde requête renvoie exactement les lignes d'une jointure complète.