Oracle Database
SI UNISCE
Ricerca…
CROSS JOIN
Un CROSS JOIN esegue un join tra due tabelle che non utilizza una clausola join esplicita e produce il prodotto cartesiano di due tabelle. Un prodotto cartesiano significa che ogni riga di una tabella è combinata con ciascuna riga della seconda tabella nel join. Ad esempio, se TABLEA ha 20 righe e TABLEB ha 20 righe, il risultato sarà 20*20 = 400 righe di output.
Esempio:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Questo può anche essere scritto come:
SELECT *
FROM TABLEA, TABLEB;
Ecco un esempio di cross join in SQL tra due tabelle:
Tabella dei campioni: TABLEA
+-------+---------+
| VALUE | NAME |
+-------+---------+
| 1 | ONE |
| 2 | TWO |
+-------+---------+
Tabella di esempio: TABELLAB
+-------+--------+
| VALUE | NAME |
+-------+--------+
| 3 | THREE |
| 4 | FOUR |
+-------+--------+
Ora, se si esegue la query:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Produzione:
+-------+--------+-------+--------+
| VALUE | NAME | VALUE | NAME |
+-------+--------+-------+--------+
| 1 | ONE | 3 | THREE |
| 1 | ONE | 4 | FOUR |
| 2 | TWO | 3 | THREE |
| 2 | TWO | 4 | FOUR |
+-------+--------+-------+--------+
Ecco come avviene l'unione incrociata tra due tabelle: 
Maggiori informazioni su Cross Join: documentazione Oracle
INNER JOIN
Un INNER JOIN è un'operazione JOIN che consente di specificare una clausola join esplicita.
Sintassi
TableExpression [INNER] JOIN TableExpression {ON booleanExpression | Clausola USING}
È possibile specificare la clausola join specificando ON con un'espressione booleana.
L'ambito delle espressioni nella clausola ON include le tabelle correnti e tutte le tabelle nei blocchi di query esterni nella SELECT corrente. Nell'esempio seguente, la clausola ON fa riferimento alle tabelle correnti:
-- 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
SINISTRA ESTERNO
A LEFT OUTER JOIN esegue un join tra due tabelle che richiede una clausola join esplicita ma non esclude righe non corrispondenti dalla prima tabella.
Esempio:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP LEFT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Anche se la sintassi ANSI è consigliata , è probabile che si verifichi la sintassi legacy molto spesso. L'uso di (+) all'interno di una condizione determina quale lato dell'equazione deve essere considerato come esterno .
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP,
SCOTT.DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO(+);
Ecco un esempio di Left Outer Join tra due tabelle:
Tabella di esempio: IMPIEGATO
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tabella dei campioni: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Ora, se si esegue la query:
SELECT
*
FROM
EMPLOYEE LEFT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Produzione:
+-----------+---------+---------+--------------+
| 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 | | |
+-----------+---------+---------+--------------+
GIUSTO ESTERNO
A RIGHT OUTER JOIN esegue un join tra due tabelle che richiede una clausola join esplicita ma non esclude righe non corrispondenti dalla seconda tabella.
Esempio:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Poiché le righe non corrispondenti di SCOTT.DEPT sono incluse, ma le righe non corrispondenti di SCOTT.EMP non lo sono, quanto sopra è equivalente alla seguente istruzione che utilizza LEFT OUTER JOIN .
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.DEPT RIGHT OUTER JOIN SCOTT.EMP
ON DEPT.DEPTNO = EMP.DEPTNO;
Ecco un esempio di Right Outer Join tra due tabelle:
Tabella di esempio: IMPIEGATO
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tabella dei campioni: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Ora, se si esegue la query:
SELECT
*
FROM
EMPLOYEE RIGHT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Produzione:
+-----------+---------+---------+--------------+
| 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'equivalente di sintassi Oracle (+) per la query è:
SELECT *
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNO(+) = DEPT.DEPTNO;
FULL OUTER JOIN
Un FULL OUTER JOIN esegue un join tra due tabelle che richiede una clausola di join esplicita ma non esclude le righe non corrispondenti in entrambe le tabelle. In altre parole, restituisce tutte le righe in ogni tabella.
Esempio:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Ecco un esempio di Full Outer Join tra due tabelle:
Tabella di esempio: IMPIEGATO
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tabella dei campioni: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Ora, se si esegue la query:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Produzione
+-----------+---------+---------+--------------+
| 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 |
+-----------+---------+---------+--------------+
Qui le colonne che non corrispondono sono state mantenute NULL.
antijoin
Un antijoin restituisce righe dal lato sinistro del predicato per il quale non ci sono righe corrispondenti sul lato destro del predicato. Restituisce le righe che non riescono a far corrispondere (NOT IN) la sottoquery sul lato destro.
SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY last_name;
Ecco un esempio di Anti Join tra due tabelle:
Tabella di esempio: IMPIEGATO
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Tabella dei campioni: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Ora, se si esegue la query:
SELECT
*
FROM
EMPLOYEE WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM DEPT);
Produzione:
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| C | 3 |
| H | 4 |
| G | 4 |
+-----------+---------+
L'output mostra che solo le righe della tabella EMPLOYEE, di cui DEPTNO non erano presenti nella tabella DEPT.
semijoin
Una query semijoin può essere utilizzata, ad esempio, per trovare tutti i reparti con almeno un dipendente il cui stipendio supera 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;
Questo è più efficiente delle alternative complete di join, poiché l'unione interna con i dipendenti e la clausola where che specifica che il salario deve essere maggiore di 2500 potrebbe restituire lo stesso dipartimento numerose volte. Diciamo che se i vigili del fuoco hanno n dipendenti con salario 3000, select * from departments, employees con il necessario join su id e la nostra clausola where restituirebbe i vigili del fuoco n volte.
ADERIRE
L'operazione JOIN esegue un join tra due tabelle, escludendo le righe non corrispondenti dalla prima tabella. Da Oracle 9i in avanti, JOIN è equivalente in funzione a INNER JOIN . Questa operazione richiede una clausola di join esplicita, a differenza degli operatori CROSS JOIN e NATURAL JOIN .
Esempio:
select t1.*,
t2.DeptId
from table_1 t1
join table_2 t2 on t2.DeptNo = t1.DeptNo
Documentazione Oracle:
JOIN NATURALE
NATURAL JOIN non richiede condizioni di unione esplosive; ne crea uno basato su tutti i campi con lo stesso nome nelle tabelle unite.
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');
Il join verrà eseguito sui campi ID e DESCR, comuni a entrambe le tabelle:
SQL> select *
2 from tab1
3 natural join
4 tab2;
ID DESCR
---------- ----------
3 three
Le colonne con nomi diversi non verranno utilizzate nella condizione 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
Se le tabelle unite non hanno colonne comuni, verrà eseguito un JOIN senza condizioni:
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
