Oracle Database
JOINT
Recherche…
CROSS JOIN
Un CROSS JOIN effectue une jointure entre deux tables qui n'utilise pas de clause de jointure explicite et génère le produit cartésien de deux tables. Un produit cartésien signifie que chaque ligne d'une table est combinée avec chaque ligne de la seconde table de la jointure. Par exemple, si TABLEA a 20 lignes et que TABLEB a 20 lignes, le résultat serait 20*20 = 400 lignes de sortie.
Exemple:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Cela peut aussi être écrit comme:
SELECT *
FROM TABLEA, TABLEB;
Voici un exemple de jointure croisée en SQL entre deux tables:
Tableau d'échantillons: TABLEA
+-------+---------+
| VALUE | NAME |
+-------+---------+
| 1 | ONE |
| 2 | TWO |
+-------+---------+
Tableau d'échantillons: TABLEB
+-------+--------+
| VALUE | NAME |
+-------+--------+
| 3 | THREE |
| 4 | FOUR |
+-------+--------+
Maintenant, si vous exécutez la requête:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Sortie:
+-------+--------+-------+--------+
| VALUE | NAME | VALUE | NAME |
+-------+--------+-------+--------+
| 1 | ONE | 3 | THREE |
| 1 | ONE | 4 | FOUR |
| 2 | TWO | 3 | THREE |
| 2 | TWO | 4 | FOUR |
+-------+--------+-------+--------+
Voici comment les jointures se produisent entre deux tables: 
En savoir plus sur Cross Join: documentation Oracle
JOINTURE INTERNE
Un INNER JOIN est une opération JOIN qui vous permet de spécifier une clause de jointure explicite.
Syntaxe
TableExpression [INNER] JOIN TableExpression {ON booleanExpression | Clause USING}
Vous pouvez spécifier la clause de jointure en spécifiant ON avec une expression booléenne.
La portée des expressions dans la clause ON inclut les tables actuelles et toutes les tables des blocs de requête externes au SELECT actuel. Dans l'exemple suivant, la clause ON fait référence aux tables en cours:
-- Join the EMP_ACT and EMPLOYEE tables
-- select all the columns from the EMP_ACT table and
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result
SELECT SAMP.EMP_ACT.*, LASTNAME
FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
-- Join the EMPLOYEE and DEPARTMENT tables,
-- select the employee number (EMPNO),
-- employee surname (LASTNAME),
-- department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the
-- DEPARTMENT table)
-- and department name (DEPTNAME)
-- of all employees who were born (BIRTHDATE) earlier than 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
FROM SAMP.EMPLOYEE JOIN SAMP.DEPARTMENT
ON WORKDEPT = DEPTNO
AND YEAR(BIRTHDATE) < 1930
-- Another example of "generating" new data values,
-- using a query which selects from a VALUES clause (which is an
-- alternate form of a fullselect).
-- This query shows how a table can be derived called "X"
-- having 2 columns "R1" and "R2" and 1 row of data
SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS VALUESTABLE1(C1, C2)
JOIN (VALUES (3, 2), (1, 2),
(0, 3)) AS VALUESTABLE2(c1, c2)
ON VALUESTABLE1.c1 = VALUESTABLE2.c1
-- This results in:
-- C1 |C2 |C1 |2
-- -----------------------------------------------
-- 3 |4 |3 |2
-- 1 |5 |1 |2
-- List every department with the employee number and
-- last name of the manager
SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT INNER JOIN EMPLOYEE
ON MGRNO = EMPNO
-- List every employee number and last name
-- with the employee number and last name of their manager
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E INNER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO
JOINTURE EXTERNE GAUCHE
Un LEFT OUTER JOIN effectue une jointure entre deux tables qui nécessite une clause de jointure explicite mais n'exclut pas les lignes sans correspondance de la première table.
Exemple:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP LEFT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Même si la syntaxe ANSI est la méthode recommandée , elle rencontrera très probablement une syntaxe héritée. L'utilisation de (+) dans une condition détermine le côté de l'équation à considérer comme externe .
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP,
SCOTT.DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO(+);
Voici un exemple de jointure externe gauche entre deux tables:
Tableau d'échantillon: EMPLOYÉ
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tableau d'échantillons: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Maintenant, si vous exécutez la requête:
SELECT
*
FROM
EMPLOYEE LEFT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Sortie:
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| F | 1 | 1 | ACCOUNTING |
| E | 1 | 1 | ACCOUNTING |
| B | 1 | 1 | ACCOUNTING |
| D | 2 | 2 | FINANCE |
| A | 2 | 2 | FINANCE |
| C | 3 | | |
| H | 4 | | |
| G | 4 | | |
+-----------+---------+---------+--------------+
DROIT EXTERIEUR
Un joint RIGHT OUTER JOIN effectue une jointure entre deux tables qui nécessite une clause de jointure explicite mais n'exclut pas les lignes sans correspondance de la seconde table.
Exemple:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Comme les lignes sans correspondance de SCOTT.DEPT sont incluses, mais que les lignes sans correspondance de SCOTT.EMP ne le sont pas, ce qui précède est équivalent à l'instruction suivante utilisant LEFT OUTER JOIN .
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.DEPT RIGHT OUTER JOIN SCOTT.EMP
ON DEPT.DEPTNO = EMP.DEPTNO;
Voici un exemple de jointure externe droite entre deux tables:
Tableau d'échantillon: EMPLOYÉ
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tableau d'échantillons: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Maintenant, si vous exécutez la requête:
SELECT
*
FROM
EMPLOYEE RIGHT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Sortie:
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| A | 2 | 2 | FINANCE |
| B | 1 | 1 | ACCOUNTING |
| D | 2 | 2 | FINANCE |
| E | 1 | 1 | ACCOUNTING |
| F | 1 | 1 | ACCOUNTING |
| | | 5 | MARKETING |
| | | 6 | HR |
+-----------+---------+---------+--------------+
L'équivalent de la syntaxe Oracle (+) pour la requête est:
SELECT *
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNO(+) = DEPT.DEPTNO;
FULL OUTER JOIN
Un FULL OUTER JOIN effectue une jointure entre deux tables qui nécessite une clause de jointure explicite mais n'exclut pas les lignes sans correspondance dans les deux tables. En d'autres termes, il renvoie toutes les lignes de chaque table.
Exemple:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Voici un exemple de jointure externe complète entre deux tables:
Tableau d'échantillon: EMPLOYÉ
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tableau d'échantillons: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Maintenant, si vous exécutez la requête:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Sortie
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| A | 2 | 2 | FINANCE |
| B | 1 | 1 | ACCOUNTING |
| C | 3 | | |
| D | 2 | 2 | FINANCE |
| E | 1 | 1 | ACCOUNTING |
| F | 1 | 1 | ACCOUNTING |
| G | 4 | | |
| H | 4 | | |
| | | 6 | HR |
| | | 5 | MARKETING |
+-----------+---------+---------+--------------+
Ici, les colonnes qui ne correspondent pas ont été conservées NULL.
ANTIJOIN
Un antijoin renvoie des lignes du côté gauche du prédicat pour lesquelles il n'y a pas de lignes correspondantes du côté droit du prédicat. Il renvoie les lignes qui ne correspondent pas (PAS à) à la sous-requête du côté droit.
SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY last_name;
Voici un exemple d'Anti Join entre deux tables:
Tableau d'échantillon: EMPLOYÉ
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tableau d'échantillons: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Maintenant, si vous exécutez la requête:
SELECT
*
FROM
EMPLOYEE WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM DEPT);
Sortie:
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| C | 3 |
| H | 4 |
| G | 4 |
+-----------+---------+
La sortie montre que seules les lignes de la table EMPLOYEE, dont DEPTNO, n'étaient pas présentes dans la table DEPT.
SEMIJOIN
Une requête de semi-jointure peut être utilisée, par exemple, pour trouver tous les départements avec au moins un employé dont le salaire dépasse 2500.
SELECT * FROM departments
WHERE EXISTS
(SELECT 1 FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
Ceci est plus efficace que les alternatives de jointure intégrale, étant donné que la jointure interne sur les employés, puis la clause précisant que le salaire doit être supérieur à 2500, peuvent retourner le même service à plusieurs reprises. Dites si le service des incendies a n employés tous avec le salaire 3000, select * from departments, employees avec les jointures nécessaires sur nos identifiants et notre clause where renverrait le service des pompiers n fois.
JOINDRE
L'opération JOIN effectue une jointure entre deux tables, à l'exclusion des lignes sans correspondance de la première table. A partir d'Oracle 9i, la fonction JOIN est équivalente en fonction à INNER JOIN . Cette opération nécessite une clause de jointure explicite, par opposition aux opérateurs CROSS JOIN et NATURAL JOIN .
Exemple:
select t1.*,
t2.DeptId
from table_1 t1
join table_2 t2 on t2.DeptNo = t1.DeptNo
Documentation Oracle:
NATURAL JOIN
NATURAL JOIN ne nécessite aucune condition de jointure explicite; il en construit un basé sur tous les champs portant le même nom dans les tables jointes.
create table tab1(id number, descr varchar2(100));
create table tab2(id number, descr varchar2(100));
insert into tab1 values(1, 'one');
insert into tab1 values(2, 'two');
insert into tab1 values(3, 'three');
insert into tab2 values(1, 'ONE');
insert into tab2 values(3, 'three');
La jointure se fera sur les champs ID et DESCR, communs aux deux tables:
SQL> select *
2 from tab1
3 natural join
4 tab2;
ID DESCR
---------- ----------
3 three
Les colonnes portant des noms différents ne seront pas utilisées dans la condition JOIN:
SQL> select *
2 from (select id as id, descr as descr1 from tab1)
3 natural join
4 (select id as id, descr as descr2 from tab2);
ID DESCR1 DESCR2
---------- ---------- ----------
1 one ONE
3 three three
Si les tables jointes n'ont pas de colonnes communes, un JOIN sans conditions sera effectué:
SQL> select *
2 from (select id as id1, descr as descr1 from tab1)
3 natural join
4 (select id as id2, descr as descr2 from tab2);
ID1 DESCR1 ID2 DESCR2
---------- ---------- ---------- ----------
1 one 1 ONE
2 two 1 ONE
3 three 1 ONE
1 one 3 three
2 two 3 three
3 three 3 three