Oracle Database
DOET MEE
Zoeken…
CROSS JOIN
Een CROSS JOIN voert een join uit tussen twee tabellen die geen expliciete join-clausule gebruikt en resulteert in het Cartesiaanse product van twee tabellen. Een Cartesiaans product betekent dat elke rij van één tafel wordt gecombineerd met elke rij van de tweede tafel in de join. Als TABLEA bijvoorbeeld 20 rijen heeft en TABLEB 20 rijen, is het resultaat 20*20 = 400 uitvoerrijen.
Voorbeeld:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Dit kan ook worden geschreven als:
SELECT *
FROM TABLEA, TABLEB;
Hier is een voorbeeld van cross join in SQL tussen twee tabellen:
Voorbeeldtabel: TABLEA
+-------+---------+
| VALUE | NAME |
+-------+---------+
| 1 | ONE |
| 2 | TWO |
+-------+---------+
Voorbeeldtabel: TABELB
+-------+--------+
| VALUE | NAME |
+-------+--------+
| 3 | THREE |
| 4 | FOUR |
+-------+--------+
Nu, als u de query uitvoert:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Output:
+-------+--------+-------+--------+
| VALUE | NAME | VALUE | NAME |
+-------+--------+-------+--------+
| 1 | ONE | 3 | THREE |
| 1 | ONE | 4 | FOUR |
| 2 | TWO | 3 | THREE |
| 2 | TWO | 4 | FOUR |
+-------+--------+-------+--------+
Dit is hoe cross joining gebeurt tussen twee tabellen: 
Meer over Cross Join: Oracle-documentatie
BINNENKANTE
Een INNER JOIN is een JOIN-bewerking waarmee u een expliciete join-clausule kunt opgeven.
Syntaxis
TableExpression [INNER] WORD LID van TableExpression {ON booleanExpression | GEBRUIK clausule}
U kunt de join-clausule opgeven door ON op te geven met een booleaanse expressie.
Het bereik van uitdrukkingen in de ON-clausule omvat de huidige tabellen en alle tabellen in buitenste queryblokken voor de huidige SELECT. In het volgende voorbeeld verwijst de clausule ON naar de huidige tabellen:
-- 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
LINKER BUITENKANT
Een LEFT OUTER JOIN voert een join uit tussen twee tabellen waarvoor een expliciete join-clausule nodig is, maar sluit niet-ongeëvenaarde rijen uit de eerste tabel uit.
Voorbeeld:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP LEFT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Hoewel de ANSI-syntaxis de aanbevolen manier is, komt deze waarschijnlijk zeer vaak in de buurt van oudere syntaxis. Het gebruik van (+) binnen een voorwaarde bepaalt welke zijde van de vergelijking als buiten moet worden beschouwd.
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP,
SCOTT.DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO(+);
Hier is een voorbeeld van Left Outer Join tussen twee tabellen:
Voorbeeldtabel: WERKNEMER
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Voorbeeldtabel: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Nu, als u de query uitvoert:
SELECT
*
FROM
EMPLOYEE LEFT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Output:
+-----------+---------+---------+--------------+
| 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 | | |
+-----------+---------+---------+--------------+
JUISTE BUITENKANT
Een RIGHT OUTER JOIN voert een join uit tussen twee tabellen waarvoor een expliciete join-clausule is vereist, maar sluit niet-ongeëvenaarde rijen uit de tweede tabel uit.
Voorbeeld:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Aangezien de niet-overeenkomende rijen van SCOTT.DEPT zijn opgenomen, maar niet-overeenkomende rijen van SCOTT.EMP niet, is het bovenstaande gelijk aan de volgende instructie met LEFT OUTER JOIN .
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.DEPT RIGHT OUTER JOIN SCOTT.EMP
ON DEPT.DEPTNO = EMP.DEPTNO;
Hier is een voorbeeld van Right Outer Join tussen twee tabellen:
Voorbeeldtabel: WERKNEMER
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Voorbeeldtabel: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Nu, als u de query uitvoert:
SELECT
*
FROM
EMPLOYEE RIGHT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Output:
+-----------+---------+---------+--------------+
| 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 |
+-----------+---------+---------+--------------+
De syntaxis van Oracle (+) voor de zoekopdracht is:
SELECT *
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNO(+) = DEPT.DEPTNO;
VOLLEDIGE BUITENKANTE
Een FULL OUTER JOIN voert een join uit tussen twee tabellen waarvoor een expliciete join-clausule nodig is, maar sluit niet-ongeëvenaarde rijen in beide tabellen niet uit. Met andere woorden, het retourneert alle rijen in elke tabel.
Voorbeeld:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Hier is een voorbeeld van Full Outer Join tussen twee tabellen:
Voorbeeldtabel: WERKNEMER
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Voorbeeldtabel: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Nu, als u de query uitvoert:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
uitgang
+-----------+---------+---------+--------------+
| 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 |
+-----------+---------+---------+--------------+
Hier zijn de kolommen die niet overeenkomen NULL gehouden.
ANTIJOIN
Een antijoin retourneert rijen vanaf de linkerkant van het predicaat waarvoor er geen overeenkomstige rijen aan de rechterkant van het predicaat zijn. Het retourneert rijen die niet overeenkomen (NIET IN) met de subquery aan de rechterkant.
SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY last_name;
Hier is een voorbeeld van Anti Join tussen twee tabellen:
Voorbeeldtabel: WERKNEMER
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Voorbeeldtabel: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Nu, als u de query uitvoert:
SELECT
*
FROM
EMPLOYEE WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM DEPT);
Output:
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| C | 3 |
| H | 4 |
| G | 4 |
+-----------+---------+
Uit de uitvoer blijkt dat alleen de rijen WERKNEMERS-tabel, waarvan DEPTNO niet aanwezig waren in DEPT-tabel.
SEMIJOIN
Een semijoin-query kan bijvoorbeeld worden gebruikt om alle afdelingen te vinden met ten minste één werknemer met een salaris van meer dan 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;
Dit is efficiënter dan de volledige join-alternatieven, omdat innerlijke toetreding tot werknemers en een clausule waarin wordt gespecificeerd dat het salaris hoger moet zijn dan 2500, dezelfde afdeling meerdere keren kunnen retourneren. Stel dat de brandweer n werknemers allemaal met salaris 3000 heeft, select * from departments, employees met de nodige join-on-id's en onze Where-clausule zou de brandweer n keer teruggeven.
JOIN
De bewerking JOIN voert een join uit tussen twee tabellen, met uitzondering van niet-overeenkomende rijen uit de eerste tabel. Vanaf Oracle 9i is de JOIN in functie gelijk aan de INNER JOIN . Deze bewerking vereist een expliciete join-clausule, in tegenstelling tot de CROSS JOIN en NATURAL JOIN operators.
Voorbeeld:
select t1.*,
t2.DeptId
from table_1 t1
join table_2 t2 on t2.DeptNo = t1.DeptNo
Oracle-documentatie:
NATUURLIJKE JOIN
NATURAL JOIN vereist geen explitische join-voorwaarde; het bouwt er een op basis van alle velden met dezelfde naam in de samengevoegde tabellen.
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');
De join wordt uitgevoerd op de velden ID en DESCR, gemeenschappelijk voor beide tabellen:
SQL> select *
2 from tab1
3 natural join
4 tab2;
ID DESCR
---------- ----------
3 three
Kolommen met verschillende namen worden niet gebruikt in de JOIN-voorwaarde:
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
Als de gekoppelde tabellen geen gemeenschappelijke kolommen hebben, wordt een JOIN zonder voorwaarden uitgevoerd:
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