Les jointures
Les jointures sont un moyen de consulter les informations provenant de plusieurs tables en même temps, sans passer par une sous-requête. Pour spécifier une jointure dans un SELECT il suffit de préciser plusieurs tables dans la clause FROM en utilisant le mot clé JOIN et de préciser la condition de jointure (qui porte généralement sur la colonne commune entre les tables (clé primaire et clé étrangère) ) :
SQL> SELECT liste_colonne FROM table1 t1 [TYPE_JOIN] JOIN table2 t2
ON table1.col1 alias1 = table2.col2 alias2
Les alias de table ‘t1’ et ‘t2’ ne sont pas obligatoires mais permettent simplement de simplifier l’écriture.
ON condition
permet de spécifier une condition de jointure indépendante des conditions de la clause WHERE
TYPE_JOIN
est facultatif. Il peut prendre les valeurs: ‘vide’, INNER, OUTER ou CROSS (voir plus loin)
ATTENTION: La condition de jointure est importante, elle est obligatoire ET DOIT AVOIR UN SENS car c’est elle qui fait la correspondance logique entre les lignes de table1 et de table2. Sans cette condition la requête ramène toutes les combinaisons possibles de lignes de table1 et de table2.
La preuve:
SQL> SELECT count(ename) FROM scott.emp JOIN scott.dept ON 1=1 COUNT(ENAME) ------------ 56
Voici un exemple simple: On veut la liste des employés et de leur lieu de travail. Le nom (ENAME) se trouve dans la table SCOTT.EMP et le lieu (LOC) dans SCOTT.DEPT.
SELECT ename, loc FROM emp JOIN dept ON emp.deptno=DEPT.deptno; ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS MILLER NEW YORK 14 rows selected.
Differents types de jointures et syntaxes
Inner joins (jointures internes)
C’est le défaut (implicite) mais ce type peut être spécifié …explicitement. Cela ramène uniquement les lignes qui satisfont la condition de jointure. On parle de JOINTURE STANDARD ou JOINTURE INTERNE (INNER JOIN)
remarque: il y a autant de lignes dans la jointure que dans EMP puisque le critère de jointure est bien vérifié pour chaque ligne (chaque employé a un no de département, chaque ligne de EMP correspond (match) une ligne de DEPT). Comme dans notre exemple précédent:
SELECT ename, loc FROM emp JOIN dept ON emp.deptno=DEPT.deptno; ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS MILLER NEW YORK 14 rows selected.
remarque: l’ancienne syntaxe supportée par Oracle, omettait le mot JOIN. La jointure était indiquée par la présence de plusieurs tables dans la clause FROM.
SQL> SELECT xxx FROM table1, table2 WHERE …;
Equijointure avec USING
USING (column) précise la condition d’équi-jointure (ou égalité entre les colonnes communes des tables).
!!! Ces colonnes doivent avoir le même nom dans les 2 tables.
Concrètement c’est souvent la clé primaire d’une table et la clé étrangère de l’autre…
SQL> SELECT ename, loc FROM emp JOIN dept 2* USING (deptno) ENAME LOC ---------- ------------- SMITH DALLAS ALLEN CHICAGO WARD CHICAGO JONES DALLAS MARTIN CHICAGO BLAKE CHICAGO CLARK NEW YORK SCOTT DALLAS KING NEW YORK TURNER CHICAGO ADAMS DALLAS JAMES CHICAGO FORD DALLAS MILLER NEW YORK 14 rows selected.
Produit cartésien des 2 tables ou jointure inconditionnelle. !!!! Attention au nombre de lignes du résultat (CF JOIN sans réelle condition de jointure)
SQL> SELECT count(*) FROM emp CROSS JOIN dept; COUNT(*) -------- 56
Jointures externes
Nous avons jusqu’ici eu de la chance dans nos jointures puisque il y une correspondance exacte entre les no de service de EMP et de DEPT.
Cependant, dans la vie courante, il n’y a parfois pas de correspondance systématique entre les valeurs des colonnes de jointures. On parle alors de jointure externe.
Si l’on regarde DEPT on voit un département 40 qui ne comporte pas d’employés dans la table EMP:
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- ---------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM emp
WHERE deptno=40;
no rows selected
et donc une jointure standard sur EMP et DEPT ne ramènera que 13 lignes: celles qui se correspondent effectivement à travers le no de département renseigné (10, 20 ou 30):
Ainsi si l’on veut la liste des départements et de leurs employés la requête naturelle serait la suivante:
SQL> SELECT d.deptno, dname, ename FROM scott.dept d JOIN scott.emp e 2 ON e.deptno=d.deptno 3 ORDER BY deptno, ename ASC 4 / DEPTNO DNAME ENAME ---------- -------------- ---------- 10 ACCOUNTING CLARK 10 ACCOUNTING KING 10 ACCOUNTING MILLER 20 RESEARCH ADAMS 20 RESEARCH FORD 20 RESEARCH JONES 20 RESEARCH SCOTT 20 RESEARCH SMITH 30 SALES ALLEN 30 SALES BLAKE 30 SALES JAMES 30 SALES MARTIN 30 SALES TURNER 30 SALES WARD 14 rows selected.
On voit bien qu’il « manque » un département.
Faire une jointure EXTERNE forcera l’affichage même lorsque la condition de jointure ne matche pas réellement.
Concrètement il existe des jointures externes ‘gauches’ (LEFT OUTER JOIN’ et ‘droite’ (RIGHT OUTER JOIN) suivant que les données sans correspondance sont respectivement dans la 1ere ou la 2eme table. Sur nos tables d’exemple c’est la table DEPT (deptno 40) pour laquelle il y a un manque…on fera donc un LEFT OUTER JOIN sur dept/emp:
SQL> SELECT d.deptno, dname, ename 2 FROM scott.dept d LEFT OUTER JOIN scott.emp e 3 ON e.deptno=d.deptno 4 ORDER BY deptno, ename ASC 5 / DEPTNO DNAME ENAME ---------- -------------- ---------- 10 ACCOUNTING CLARK 10 ACCOUNTING KING 10 ACCOUNTING MILLER 20 RESEARCH ADAMS 20 RESEARCH FORD 20 RESEARCH JONES 20 RESEARCH SCOTT 20 RESEARCH SMITH 30 SALES ALLEN 30 SALES BLAKE 30 SALES JAMES 30 SALES MARTIN 30 SALES TURNER 30 SALES WARD 40 OPERATIONS 15 rows selected.
On retrouve bien une ligne de plus (15 au lieu de 14) et le département no 40: ‘OPERATIONS’ manquant…avec ses 0 employés!
Cette requête est (par définition) équivallente à la suivante (symétriquement! : on précise d’abord la table EMP PUIS la table DEPT (qui passe donc à droite mais qui présente toujours la lacune de correspondance) et le LEFT OUTER JOIN devient un RIGHT OUTER JOIN:
SQL> SELECT d.deptno, dname, ename FROM scott.emp e RIGHT OUTER JOIN scott.dept d ON e.deptno=d.deptno ORDER BY deptno, ename ASC
DEPTNO DNAME ENAME ---------- -------------- ---------- 10 ACCOUNTING CLARK 10 ACCOUNTING KING 10 ACCOUNTING MILLER 20 RESEARCH ADAMS 20 RESEARCH FORD 20 RESEARCH JONES 20 RESEARCH SCOTT 20 RESEARCH SMITH 30 SALES ALLEN 30 SALES BLAKE 30 SALES JAMES 30 SALES MARTIN 30 SALES TURNER 30 SALES WARD 40 OPERATIONS 15 rows selected.
Auto jointures
Il est possible de faire des jointures d’une table sur elle même. Par exemple si l’on veut les noms des employés et de leurs chefs il faudrait balayer manuellement 2 fois la table…ou faire une auto jointure:
SQL> select emp.ename employe, chef.ename chef from emp JOIN emp chef ON emp.mgr = chef.empno; EMPLOYE CHEF ---------- ---------- FORD JONES SCOTT JONES TURNER BLAKE ALLEN BLAKE WARD BLAKE JAMES BLAKE MARTIN BLAKE MILLER CLARK ADAMS SCOTT BLAKE KING JONES KING CLARK KING SMITH FORD
On notera que sur cette requête la correspondance entre les lignes des 2 tables virtuelles, se fait non pas sur un lien Clé primaire / étrangère mais sur un lien plus ‘sémantique’ entre no de chef et no d’employé qui caractérise dans cette table la hiérarchie des employés.
On notera également qu’il n’apparait que 13 lignes car Mr KING président de la société n’a pas de chef…Si on voulait le faire apparaitre quand même, il aurait fallu faire une jointure externe…
Alternatives aux Jointures
Dans certains cas (AUTO JOINTURE notamment) une sous-requête peut fournir le même résultat fonctionnellement qu’une jointure. Cependant les performances peuvent différer.
Il est aussi possible d’accéder à des données de plusieurs tables SANS jointure…en faisant des SELECT en lieu et place des colonnes d’un SELECT principal.
Ainsi pour avoir les noms de EMP et les nom de département de DEPT on peut mettre le 2e SELECT DANS la SELECT LIST du premier comme suit:
SQL> select ename nom, 2 (select dname from dept d where d.deptno=e.deptno) service 3 from emp e 4 / NOM SERVICE ---------- -------------- SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 rows selected.