Le type de jointure le plus utilisé, Inner Join, renvoie les lignes qui ont des correspondances dans les deux tables, et les renvoie autant de fois qu’il y a de correspondances. Par exemple, une jointure entre DEPT et EMP ne renverra pas les informations des départements qui n’ont pas d’employés, et renverra plusieurs fois les informations des départements qui ont plusieurs employés.
Il y a des variantes: on peut choisir de ne renvoyer qu’une seule fois les infos d’une table même s’il y a plusieurs correspondances dans l’autre: c’est un Semi Join (clause EXISTS ou IN) et on peut renvoyer au contraire celles pour lesquelles il n’y a pas de correspondance (NOT EXISTS ou NOT IN).
Et on peut combiner cela: un Inner Join qui renvoie en plus les infos d’une table même si il n’y a pas de correspondance. Il s’agit d’un Outer Join qui va rajouter (union all) à la jointure Inner Join les lignes venant d’un Anti-Join.
DEPT Left Outer Join EMP renvoie toutes les lignes de DEPT. Une seule fois (et avec des null pour les colonnes de EMP) lorsqu’il n’y a pas d’employés dans le département. Et plusieurs fois lorsqu’il y a plusieurs employés dans le département. On dit que la table DEPT est ici ‘preserved': on n’élimine aucune de ses infos.
Inversement, DEPT Right Outer Join EMP renvoit tous les employés même si on n’a pas d’info dans DEPT. Bien sûr, ce n’est pas le cas lorsqu’on a déclaré la Foreign Key.
Et DEPT full outer join EMP va combiner les deux
La demo montre ces jointures externes (left, right et full) en for̤ant avec des hints toutes les m̩thodes de jointures possible Рet dans chaque ordre possible Рafin de d̩terminer quelles sont les m̩thodes compatibles avec Outer Join.
Il n’y a que Hash Join qui a la possibilité de faire des Hash Join dans les deux sens: quelle que soit la ‘preserved’ table, il est toujours possible de choisir l’une ou l’autre pour construire la table de hachage. Les autres méthodes n’acceptent pas la ‘preserved table’ en second.
Ce qui veut dire que le ‘Full Outer join’ n’est possible directement (sans union all) que par Hash Join
Avec EMP comme table de hachage:
select /*+ leading(dept emp) use_hash(emp) swap_join_inputs(emp) */ * from DEPT full outer join EMP using(deptno) ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 15 | | | | | 1 | VIEW | VW_FOJ_0 | 1 | 9 | 10 |00:00:00.01 | 15 | | | | |* 2 | HASH JOIN FULL OUTER| | 1 | 9 | 10 |00:00:00.01 | 15 | 1055K| 1055K| 517K (0)| | 3 | TABLE ACCESS FULL | EMP | 1 | 8 | 8 |00:00:00.01 | 7 | | | | | 4 | TABLE ACCESS FULL | DEPT | 1 | 3 | 3 |00:00:00.01 | 8 | | | | -----------------------------------------------------------------------------------------------------------------------
Avec DEPT comme table de hachage:
select /*+ leading(dept emp) use_hash(emp) no_swap_join_inputs(emp) */ * from DEPT full outer join EMP using(deptno) ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 15 | | | | | 1 | VIEW | VW_FOJ_0 | 1 | 9 | 10 |00:00:00.01 | 15 | | | | |* 2 | HASH JOIN FULL OUTER| | 1 | 9 | 10 |00:00:00.01 | 15 | 1321K| 1321K| 636K (0)| | 3 | TABLE ACCESS FULL | DEPT | 1 | 3 | 3 |00:00:00.01 | 7 | | | | | 4 | TABLE ACCESS FULL | EMP | 1 | 8 | 8 |00:00:00.01 | 8 | | | | ----------------------------------------------------------------------------------------------------------------------