Cours SQL ORACLE – 03 : Les jointures (LEFT RIGHT FULL JOIN).
Les jointures permettent de joindre les lignes de plusieurs tables dans la même requête. Pour mieux expliquer cela, prenons comme exemple deux tables : commandes et lignes commandes. La table des commandes comporte l’entête de chaque commande (référence de la commande, date de la commande, client etc…) alors que la table ligne commande comporte les détails de chaque ligne de commandes (article, quantité, etc…). Pour afficher une commande au complet, nous devons faire une jointure entre ces deux tables.
Il existe plusieurs types de jointure. Nous allons essayer de les voir toutes en détails :
- Produit cartésien :
Comme en mathématique, le produit cartésien est l’ensemble de tous les couples composé par les lignes de la table 1 et la table2.
Pour réaliser, cette jointure :
SELECT * FROM table1, table2
Prenons le cas des deux tables EMP et DEPT du schéma SCOTT. Le contenu de ces deux tables est :
Table EMP :
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
| 7369 | SMITH | CLERK | 7902 | 17/12/1980 | 800 | 20 | |
| 7876 | ADAMS | CLERK | 7788 | 23/05/1987 | 1100 | 20 | |
| 7521 | WARD | SALESMAN | 7698 | 22/02/1981 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 28/09/1981 | 1250 | 1400 | 30 |
| 7934 | MILLER | CLERK | 7782 | 23/01/1982 | 1300 | 10 | |
| 7844 | TURNER | SALESMAN | 7698 | 08/09/1981 | 1500 | 0 | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 20/02/1981 | 1600 | 300 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 09/06/1981 | 2450 | 10 | |
| 7698 | BLAKE | MANAGER | 7839 | 01/05/1981 | 2850 | 30 | |
| 7566 | JONES | MANAGER | 7839 | 02/04/1981 | 2975 | 20 | |
| 7902 | FORD | ANALYST | 7566 | 03/12/1981 | 3000 | 20 | |
| 7788 | SCOTT | ANALYST | 7566 | 19/04/1987 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 17/11/1981 | 5000 | 10 |
Table DEPT:
| DEPTNO | DNAME | LOC |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
Exécutons maintenant le produit cartésien de ces deux tables :
SELECT
EMP.EMPNO, EMP.ENAME, EMP.HIREDATE, EMP.DEPTNO, DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC
FROM EMP, DEPT;
Le résultat sera :
| EMPNO | ENAME | HIREDATE | DEPTNO | DEPTNO_1 | DNAME | LOC |
| 7369 | SMITH | 17/12/1980 | 20 | 10 | ACCOUNTING | NEW YORK |
| 7499 | ALLEN | 20/02/1981 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7521 | WARD | 22/02/1981 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7566 | JONES | 02/04/1981 | 20 | 10 | ACCOUNTING | NEW YORK |
| 7654 | MARTIN | 28/09/1981 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7698 | BLAKE | 01/05/1981 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | 09/06/1981 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | 19/04/1987 | 20 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | 17/11/1981 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | 08/09/1981 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7876 | ADAMS | 23/05/1987 | 20 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | 03/12/1981 | 20 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | 23/01/1982 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | 17/12/1980 | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | 20/02/1981 | 30 | 20 | RESEARCH | DALLAS |
| 7521 | WARD | 22/02/1981 | 30 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | 02/04/1981 | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | 28/09/1981 | 30 | 20 | RESEARCH | DALLAS |
| 7698 | BLAKE | 01/05/1981 | 30 | 20 | RESEARCH | DALLAS |
| 7782 | CLARK | 09/06/1981 | 10 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | 19/04/1987 | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | 17/11/1981 | 10 | 20 | RESEARCH | DALLAS |
| 7844 | TURNER | 08/09/1981 | 30 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | 23/05/1987 | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | 03/12/1981 | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | 23/01/1982 | 10 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | 17/12/1980 | 20 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | 20/02/1981 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | 22/02/1981 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | 02/04/1981 | 20 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | 28/09/1981 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | 01/05/1981 | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | 09/06/1981 | 10 | 30 | SALES | CHICAGO |
| 7788 | SCOTT | 19/04/1987 | 20 | 30 | SALES | CHICAGO |
| 7839 | KING | 17/11/1981 | 10 | 30 | SALES | CHICAGO |
| 7844 | TURNER | 08/09/1981 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | 23/05/1987 | 20 | 30 | SALES | CHICAGO |
| 7902 | FORD | 03/12/1981 | 20 | 30 | SALES | CHICAGO |
| 7934 | MILLER | 23/01/1982 | 10 | 30 | SALES | CHICAGO |
| 7369 | SMITH | 17/12/1980 | 20 | 40 | OPERATIONS | BOSTON |
| 7499 | ALLEN | 20/02/1981 | 30 | 40 | OPERATIONS | BOSTON |
| 7521 | WARD | 22/02/1981 | 30 | 40 | OPERATIONS | BOSTON |
| 7566 | JONES | 02/04/1981 | 20 | 40 | OPERATIONS | BOSTON |
| 7654 | MARTIN | 28/09/1981 | 30 | 40 | OPERATIONS | BOSTON |
| 7698 | BLAKE | 01/05/1981 | 30 | 40 | OPERATIONS | BOSTON |
| 7782 | CLARK | 09/06/1981 | 10 | 40 | OPERATIONS | BOSTON |
| 7788 | SCOTT | 19/04/1987 | 20 | 40 | OPERATIONS | BOSTON |
| 7839 | KING | 17/11/1981 | 10 | 40 | OPERATIONS | BOSTON |
| 7844 | TURNER | 08/09/1981 | 30 | 40 | OPERATIONS | BOSTON |
| 7876 | ADAMS | 23/05/1987 | 20 | 40 | OPERATIONS | BOSTON |
| 7902 | FORD | 03/12/1981 | 20 | 40 | OPERATIONS | BOSTON |
| 7934 | MILLER | 23/01/1982 | 10 | 40 | OPERATIONS | BOSTON |
Vous verrez que le résultat affiché est l’ensemble des couples composés par les lignes des deux tables.
Dans les cas pratiques, on utilise très rarement ce genre de jointure.
- table1.id = table2.id
Ce genre de jointure est le plus utilisé. Prenons les mêmes tables déjà utilisé ci-dessus :
SELECT
EMP.EMPNO, EMP.ENAME, EMP.HIREDATE, EMP.DEPTNO, DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC
FROM EMP, DEPT
WHERE EMP.deptno = DEPT.deptno;
Cette jointure est réalisée entre les deux tables EMP et SAL. Généralement les jointures se font à travers les clés étrangères et primaires des tables. Dans notre cas, deptno est la clé étrangère dans la table EMP alors qu’elle clé primaire de la table SAL. Du coup, le résultat de cette requête sera :
| EMPNO | ENAME | HIREDATE | DEPTNO | DEPTNO_1 | DNAME | LOC |
| 7782 | CLARK | 09/06/1981 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | 17/11/1981 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | 23/01/1982 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7566 | JONES | 02/04/1981 | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | 19/04/1987 | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | 03/12/1981 | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | 17/12/1980 | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | 23/05/1987 | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | 20/02/1981 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | 22/02/1981 | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | 08/09/1981 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | 01/05/1981 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | 28/09/1981 | 30 | 30 | SALES | CHICAGO |
Vous pouvez constater que toutes les lignes de la table EMP ont été ramenées par la requête. En plus, les informations relatives au département ont été affichées aussi. Vous pouvez aussi voir que les données du département 40 n’ont pas été sélectionnées puisqu’aucun employé n’appartient au département 40.
Lançant maintenant cette requête :
SELECT
EMPLOYE.EMPNO NUM_EMP, EMPLOYE.ENAME NOM_EMP, EMPLOYE.JOB POSTE_EMP, MANAGER.ENAME NOM_MANAGER, MANAGER.JOB POSTE_MANAGER, DEPT.DNAME DEPART
FROM EMP EMPLOYE, EMP MANAGER, DEPT
WHERE EMPLOYE.DEPTNO = DEPT.DEPTNO AND EMPLOYE.MGR = MANAGER.EMPNO;
Le résultat obtenu est :
| NUM_EMP | NOM_EMP | POSTE_EMP | NOM_MANAGER | POSTE_ MANAGER | DEPART |
| 7788 | SCOTT | ANALYST | JONES | MANAGER | RESEARCH |
| 7902 | FORD | ANALYST | JONES | MANAGER | RESEARCH |
| 7499 | ALLEN | SALESMAN | BLAKE | MANAGER | SALES |
| 7521 | WARD | SALESMAN | BLAKE | MANAGER | SALES |
| 7654 | MARTIN | SALESMAN | BLAKE | MANAGER | SALES |
| 7844 | TURNER | SALESMAN | BLAKE | MANAGER | SALES |
| 7934 | MILLER | CLERK | CLARK | MANAGER | ACCOUNTING |
| 7876 | ADAMS | CLERK | SCOTT | ANALYST | RESEARCH |
| 7698 | BLAKE | MANAGER | KING | PRESIDENT | SALES |
| 7566 | JONES | MANAGER | KING | PRESIDENT | RESEARCH |
| 7782 | CLARK | MANAGER | KING | PRESIDENT | ACCOUNTING |
| 7369 | SMITH | CLERK | FORD | ANALYST | RESEARCH |
Dans cette requête, en plus de la jointure entre les tables EMP et DEPT, nous avons ajouté une nouvelle jointure entre la table EMP et elle-même. La condition de cette dernière jointure est entre l’attribut MGR de la première table EMP (EMPLOYE) et l’attribut EMPNO de la deuxième table EMP (MANAGER). Nous avons affiché donc les num des employés, leurs noms, leurs postes, les noms de leurs managers, les postes des managers et les départements des employés.
Remarque : L’employé 7839 n’a pas a été sélectionné parce que l’attribut MNG est nul.
- INNER JOIN
Les jointures INNER JOIN ou JOIN ont le même résultat que celle du paragraphe précédent.
Syntaxe :
SELECT
*
FROM table1
INNER JOIN table2 ON table1.id = table2.id;
Reprenons les deux requête du paragraphe précédent mais en utilisant INNER JOIN:
SELECT
EMP.EMPNO, EMP.ENAME, EMP.MGR, EMP.HIREDATE, EMP.DEPTNO, DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC
FROM EMP
INNER JOIN DEPT ON EMP.deptno = DEPT.deptno;
SELECT
EMPLOYE.EMPNO NUM_EMP, EMPLOYE.ENAME NOM_EMP, EMPLOYE.JOB POSTE_EMP, MANAGER.ENAME NOM_MANAGER, MANAGER.JOB POSTE_MANAGER, DEPT.DNAME DEPART
FROM EMP EMPLOYE
JOIN EMP MANAGER ON EMPLOYE.MGR = MANAGER.EMPNO
JOIN DEPT ON EMPLOYE.DEPTNO = DEPT.DEPTNO;
La condition de jointure est ajoutée après ON. Nous pouvons ajouter un WHERE après avoir faire la jointure. Par exemple, nous allons ajouter dans cette dernière requête une condition pour ne sélectionner que les employés qui ont un poste ANALYST
SELECT
EMPLOYE.EMPNO NUM_EMP, EMPLOYE.ENAME NOM_EMP, EMPLOYE.JOB POSTE_EMP, MANAGER.ENAME NOM_MANAGER, MANAGER.JOB POSTE_MANAGER, DEPT.DNAME DEPART
FROM EMP EMPLOYE
JOIN EMP MANAGER ON EMPLOYE.MGR = MANAGER.EMPNO
JOIN DEPT ON EMPLOYE.DEPTNO = DEPT.DEPTNO
WHERE EMPLOYE.JOB = 'ANALYST';
Le résultat obtenu dans ce cas est :
| NUM_EMP | NOM_EMP | POSTE_EMP | NOM_MANAGER | POSTE_ MANAGER | DEPART |
| 7788 | SCOTT | ANALYST | JONES | MANAGER | RESEARCH |
| 7902 | FORD | ANALYST | JONES | MANAGER | RESEARCH |
- FULL, RIGHT et LEFT JOIN (jointure externe)
Ce type de jointure externe est très intéressant. Dans notre requête qui affiche les employés et leurs Manager, une ligne n’a pas été sélectionnée. Le Manager de cette ligne est vide. Mais si on voulait avoir les données de tous les employés (qui ont ou pas un Manager) avec les données de leurs Mangers s’elles existent, la requête sera alors :
SELECT
EMPLOYE.EMPNO NUM_EMP, EMPLOYE.ENAME NOM_EMP, EMPLOYE.JOB POSTE_EMP, MANAGER.ENAME NOM_MANAGER, MANAGER.JOB POSTE_MANAGER, DEPT.DNAME DEPART
FROM EMP EMPLOYE
LEFT JOIN EMP MANAGER ON EMPLOYE.MGR = MANAGER.EMPNO
JOIN DEPT ON EMPLOYE.DEPTNO = DEPT.DEPTNO;
Le résultat est :
| NUM_EMP | NOM_EMP | POSTE_EMP | NOM_NPLUS1 | POSTE_NPLUS1 | DEPART |
| 7902 | FORD | ANALYST | JONES | MANAGER | RESEARCH |
| 7788 | SCOTT | ANALYST | JONES | MANAGER | RESEARCH |
| 7654 | MARTIN | SALESMAN | BLAKE | MANAGER | SALES |
| 7844 | TURNER | SALESMAN | BLAKE | MANAGER | SALES |
| 7521 | WARD | SALESMAN | BLAKE | MANAGER | SALES |
| 7499 | ALLEN | SALESMAN | BLAKE | MANAGER | SALES |
| 7934 | MILLER | CLERK | CLARK | MANAGER | ACCOUNTING |
| 7876 | ADAMS | CLERK | SCOTT | ANALYST | RESEARCH |
| 7698 | BLAKE | MANAGER | KING | PRESIDENT | SALES |
| 7566 | JONES | MANAGER | KING | PRESIDENT | RESEARCH |
| 7782 | CLARK | MANAGER | KING | PRESIDENT | ACCOUNTING |
| 7369 | SMITH | CLERK | FORD | ANALYST | RESEARCH |
| 7839 | KING | PRESIDENT | ACCOUNTING |
Vous pouvez voir que l’employé 7839 est sélectionné et que les attributs NOM_NPLUS1 et POSTE_NPLUS1 sont vide.
De la même manière, RIGHT JOIN a le même comportement que LEFT JOIN mais au sens contraire.
EMP EMPLOYE LEFT JOIN EMP MANAGER ON EMPLOYE.MGR = MANAGER.EMPNO est équivalent à EMP MANAGER RIGHT JOIN EMP EMPLOYE ON EMPLOYE.MGR = MANAGER.EMPNO
FULL JOIN est une jointure dans les deux sens. Pour mieux comprendre cette jointure, faisons l’exemple suivant :
Ajoutons une ligne dans la table EMP
INSERT INTO EMP VALUES (1, 'NOM', 'JOB', 7902, '01/01/1980', 1234, NULL, 60);
Exécutons la requête ci-dessous :
SELECT
EMP.EMPNO, EMP.ENAME, EMP.HIREDATE, EMP.DEPTNO, DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC FROM EMP
FULL JOIN DEPT ON EMP.deptno = DEPT.deptno;
Le résultat sera
| EMPNO | ENAME | HIREDATE | DEPTNO | DEPTNO_1 | DNAME | LOC |
| 7782 | CLARK | 09/06/1981 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | 17/11/1981 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | 23/01/1982 | 10 | 10 | ACCOUNTING | NEW YORK |
| 7566 | JONES | 02/04/1981 | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | 19/04/1987 | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | 03/12/1981 | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | 17/12/1980 | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | 23/05/1987 | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | 20/02/1981 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | 22/02/1981 | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | 08/09/1981 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | 01/05/1981 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | 28/09/1981 | 30 | 30 | SALES | CHICAGO |
| 1 | NOM | 01/01/1980 | 60 | |||
| 40 | OPERATIONS | BOSTON |
Vous pouvez constater que toutes les lignes des deux tables sont affichées même ceux qui n’ont pas d’équivalence.
Pour finir ce cours, il faut savoir que nous pouvons réaliser ces dernières jointures (FULL, RIGHT et LEFT JOIN) en utilisant les jointures ordinaires. Pour se faire, nous utilisons le symbole (+)
FULL JOIN correspond à table1.id (+) = table2.id (+)
RIGHT JOIN correspond à table1.id (+) = table2.id
LEFT JOIN correspond à table1.id = table2.id (+)