OUTER JOIN
Outer join
Als je rijen uit meerdere tabellen ophaalt, kan het zijn dat sommige rijen uit de ene tabel niet gekoppeld (ge-joined) kunnen worden (door de join-voorwaarde) met een rij uit de andere tabel. Zo'n rij komt dan niet in je query resultaat. Soms wil je die rijen uit die ene tabel toch in je resultaat. Door gebruik te maken van een outer-join zal het DBMS deze rijen dan toch in je resultaat behouden, waarbij ze ge-joined worden met een (denkbeeldige) geheel lege rij (allemaal NULL's) uit de andere tabel. In het voorbeeld van onze database heeft werknemer Grant bijvoorbeeld geen department_id (ze werkt dus niet op een bepaalde afdeling) en er is een department waar niemand werkt (Contracting).
Als we de tabellen employees en departments samenvoegen met een INNER JOIN krijgen we een uitvoer van 19 records terwijl er 20 werknemers zijn. Grant staat hier namelijk niet bij.
SELECT first_name, last_name, department_id, department_name
FROM employees
JOIN departments USING(department_id);
Dit soort problemen kunnen we oplossen met LEFT JOIN en RIGHT JOIN.
Left join
Bij een LEFT JOIN worden rijen uit de linker tabel in het query resultaat behouden als ze niet met een rij uit de rechter tabel gejoined kunnen worden. In plaats van LEFT JOIN wordt ook wel LEFT OUTER JOIN gebruikt. In de eerste tabel komen zwart, blauw en wit voor en in de tweede tabel blauw, wit en oranje. Met een LEFT JOIN worden alle rijen getoond van de linker tabel, dus in dit geval zwart, blauw en wit. Maar van de rechter tabel wordt alleen maar blauw en wit getoond en oranje niet.
SELECT last_name, department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
| last_name | department_name |
|---|---|
| King | Executive |
| Kochhar | Executive |
| De Haan | Executive |
| Hunold | IT |
| Ernst | IT |
| Lorentz | IT |
| Mourgos | Shipping |
| Rajs | Shipping |
| Davies | Shipping |
| Matos | Shipping |
| Vargas | Shipping |
| Zlotkey | Sales |
| Abel | Sales |
| Taylor | Sales |
| Grant | NULL |
| Whalen | Administration |
| Hartstein | Marketing |
| Fay | Marketing |
| Higgins | Accounting |
| Gietz | Accounting |
Je ziet dat Grant nu wel in het linker rijtje voorkomt, ondanks het feit dat ze geen department_name heeft. En dat komt dus door de toevoeging van LEFT bij de JOIN. Bij een LEFT JOIN is het linkerrijtje het langst.
Right join
Bij een RIGHT JOIN worden rijen uit de rechter tabel in het query resultaat behouden als ze niet met een rij uit de linker tabel gejoined kunnen worden. In plaats van RIGHT JOIN wordt ook wel RIGHT OUTER JOIN gebruikt. IN de linker tabel komen zwart, blauw en wit voor en in de rechter tabel blauw, wit en oranje. Zwart wordt dus niet getoond, want die staat alleen maar aan de linkerkant. Oranje wordt wel getoond.
SELECT last_name, department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
| last_name | department_name |
|---|---|
| King | Executive |
| Kochhar | Executive |
| De Haan | Executive |
| Hunold | IT |
| Ernst | IT |
| Lorentz | IT |
| Mourgos | Shipping |
| Rajs | Shipping |
| Davies | Shipping |
| Matos | Shipping |
| Vargas | Shipping |
| Zlotkey | Sales |
| Abel | Sales |
| Taylor | Sales |
| Whalen | Administration |
| Hartstein | Marketing |
| Fay | Marketing |
| Higgins | Accounting |
| Gietz | Accounting |
| NULL | Contracting |
Je ziet dat Contracting nu wel in het rechterrijtje voorkomt, ondanks het feit dat er niemand op die afdeling werkt. En dat komt dus door de toevoeging van RIGHT bij de JOIN. Bij een RIGHT JOIN is het rechterrijtje het langst.
In de praktijk worden RIGHT JOINS niet gebruikt. Het is niet erg nuttig een overzicht te laten beginnen met een kolom met NULL. Bovendien kun je een OUTER JOIN namaken met een LEFT JOIN door de kolomnamen om te draaien.
SELECT department_name, last_name
FROM departments
LEFT JOIN employees ON departments.department_id = employees.department_id;
Full join
Je kunt een LEFT JOIN en een RIGHT JOIN combineren tot een FULL JOIN. Je krijgt dan alle rijen van een kolom uit de ene tabel gecombineerd met alle rijen van een kolom uit een andere tabel, ongeacht of er een match is of niet. In plaats van FULL JOIN wordt ook wel FULL OUTER JOIN gebruikt.
SELECT employees.last_name, departments.department_id, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
Zowel Grant als Contracting komen op deze manier tevoorschijn.
| |
De FULL OUTER JOIN is ANSI-standaard maar wordt niet ondersteund in MySQL.
Self join
Het is mogelijk een tabel met zichzelf te joinen door twee aliassen te gebruiken voor dezelfde tabel. De database denkt dan dat er twee tabellen zijn. In de tabel employees staat bij manager_id wie de manager is van de employee. Dat nummer bij manager_id verwijst naar het employee_id.
- Je ziet dat King geen manager_id heeft, hij is de hoogste baas.
- Kochar heeft als manager_id 100 en dat is dus King.
- Hunold heeft als manager_id 102 en dat is dus De Haan.
- et cetera
Als je een alias gaat kiezen, moet je een duidelijke naam nemen. In dit geval hebben we worker voor de werknemers gekozen en manager voor de managers. We kunnen nu een overzicht maken met de werknemers en hun managers.
SELECT worker.last_name, worker.manager_id, manager.last_name AS manager_name
FROM employees worker
JOIN employees manager ON worker.manager_id = manager.employee_id;
En als je daar KING bij wil hebben, maak je er een LEFT JOIN van.
SELECT worker.last_name, worker.manager_id, manager.last_name AS manager_name
FROM employees worker
LEFT JOIN employees manager ON worker.manager_id = manager.employee_id;
De SELF JOIN komt sporadisch voor en is dus niet erg belangrijk. In de database die wij gebruiken is er slechts één SELF-JOIN te maken en die staat hierboven uitgelegd.