OUTER JOIN: de "gaten" vinden

In Module 3 zag je LEFT JOIN: houd alle rijen aan de linkerkant, zelfs die zonder een match aan de rechterkant (die NULL wordt). In dit hoofdstuk wij breid de toolkit uit met RIGHT JOIN en FULL OUTER JOIN, en we leren hoe je ze kunt gebruiken om de "gaten" in de gegevens te vinden: rijen in één tabel die hebben geen tegenhanger in de ander.

De drie soorten OUTER JOIN

A LEFT  JOIN B  ON …  -- tutte le righe di A, con o senza match in B
A RIGHT JOIN B  ON …  -- tutte le righe di B, con o senza match in A
A FULL  JOIN B  ON …  -- tutte le righe di A *e* di B; NULL dove non c'è match

A RIGHT JOIN B kan altijd worden herschreven als B LEFT JOIN A met dezelfde ON: het is een kwestie van leesbaarheid. Veel stijlen (inclusief deze cursus) gebruik liever altijd LEFT JOIN voor consistentie.

Het "anti-join"-patroon: het vinden van de ontbrekende

Het meest voorkomende gebruik van LEFT JOIN is niet "verrijken met de gegevens op de right", maar zoek de rijen aan de linkerkant die NIET overeenkomen met de juist. Het heet een anti-join:

-- Prodotti senza nessuna recensione:
SELECT p.id, p.name
FROM   products p
LEFT JOIN reviews r ON r.product_id = p.id
WHERE  r.id IS NULL
ORDER BY p.id;

Patroonlogica:

1. LEFT JOIN voegt eventuele recensies van elk product toe;
2. voor producten zonder recensies zijn alle r kolommen NULL;
3. WHERE r.id IS NULL bewaart alleen deze producten.

Sleutelcontrole: een id IS NULL op een NOT NULL kolom van de rechtertabel betekent "er was geen overeenkomst". Je filtert vaak op de PK van de rechterhand juist om deze reden op tafel.

VOLLEDIGE BUITENKOM

FULL OUTER JOIN zorgt ervoor dat beide partijen geen match hebben. Het wordt zelden gebruikt, doorgaans voor gegevensintegriteitscontroles:

SELECT c.id AS customer_id, o.id AS order_id
FROM   customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
WHERE  c.id IS NULL OR o.id IS NULL;

Rijen met c.id IS NULL zouden weesbestellingen zijn (een customer_id die bestaat niet in customers). Degenen met o.id IS NULL zijn klanten zonder bestellingen. Op onze dataset garanderen de externe sleutels dat er geen wees is bestellingen bestaan; alleen de klanten zonder bestellingen blijven over.

Probeer het zelf

Oefening beoordelen