SQL-joins en tabelrelaties

Het adres van een klant hoort bij de klant. De datum van een bestelling hoort bij de bestelling. Door deze gegevens in aparte tabellen te bewaren, verminder je herhaling, maar een rapport heeft vaak gegevens uit beide nodig. Een join combineert hun rijen binnen een query.

Lees de sleutels

Een primaire sleutel identificeert één rij. Het kan één kolom zijn of meerdere kolommen samen. Een refererende sleutel verwijst naar een primaire sleutel of een andere unieke sleutel, in een andere tabel of in dezelfde tabel. In de winkeldatabase verwijst orders.customer_id naar customers.customer_id.

Bestellingen 101 en 102 verwijzen allebei naar klant 1, Ana. Bestelling 103 verwijst naar Ben. Bestelling 104 is een bestelling als gast: de klantsleutel is NULL en verwijst dus naar geen enkele klant. Het bestelnummer identificeert de bestelling, niet de klant. Als je order_id aan customer_id koppelt, vergelijk je identificatiewaarden die niets met elkaar te maken hebben.

Benoem de relatie

De klant-bestelrelatie is een-op-veel. Een klant kan geen, één of meerdere bestellingen hebben. Dat betekent niet dat de klanttabel voor elke aankoop een bestel-ID opslaat. Elke bestelling slaat haar eigen klantverwijzing op.

Bij studenten en cursussen kan geen van beide kanten met slechts één verwijzing alle koppelingen beschrijven. Een rij in enrollments legt één student-cursuspaar vast. Meerdere inschrijvingsrijen kunnen naar dezelfde student verwijzen en meerdere naar dezelfde cursus.

Volg een route voordat je SQL schrijft

Om producten te tonen die elke klant heeft gekocht, volg je customers → orders → order_items → products. De koppelingen gebruiken achtereenvolgens customer_id, order_id en product_id. Elke koppeling heeft een eigen voorwaarde nodig. Een orderregel levert de ontbrekende verbinding tussen een bestelling en een product.

Lees wat het schema betekent en kijk niet alleen naar de spelling van kolommen. employees.manager_id kan verwijzen naar employees.emp_id, ook al verschillen de namen. Omgekeerd benoemen customers.name en products.name verschillende soorten zaken. Je moet ze niet koppelen alleen omdat beide name heten.

Een vastgelegde refererende sleutel is een nuttige aanwijzing, maar SQL kan ook tabellen koppelen zonder vastgelegde integriteitsregel. Een prijs kan overeenkomen met een prijsklasse en twee werknemers kunnen overeenkomen omdat ze dezelfde leidinggevende hebben. De voorwaarde moet de relatie uitdrukken die voor de vraag nodig is.

Een join bouwt nieuwe rijen op door invoerrijen te combineren. Begin met de koppelingsregel en vraag je af hoeveel overeenkomsten elke rij heeft. Het antwoord bepaalt zowel welke rijen verschijnen als hoe vaak hun waarden worden herhaald.

Denk in paren

Stel je bij een inner join elke klant naast elke bestelling voor. Behoud een paar alleen als o.customer_id = c.customer_id waar is. Bestelling 101 wordt gekoppeld aan Ana, niet aan Ben, Chloe of Dev. Ook bestelling 102 wordt aan Ana gekoppeld. Eén klant kan dus meerdere resultaatrijen opleveren.

De voorwaarde kan waar, onwaar of onbekend zijn. Een gelijkheid met NULL is onbekend, dus gastbestelling 104 vindt geen overeenkomende klant. Een outer join behoudt ook rijen zonder overeenkomst aan de gekozen zijde en vult de kolommen van de ontbrekende tegenhanger met NULL.

Dit beeld van paren legt joins beter uit dan overlappende cirkels. Een join verzamelt niet alleen gedeelde items: hij bouwt gecombineerde rijen op en herhaalde sleutels kunnen meer uitvoerrijen opleveren dan elk van de invoertabellen bevat. Als twee rijen aan de ene kant en drie aan de andere een sleutel delen, levert die sleutel zes overeenkomende paren op.

Kies kolommen los van de overeenkomsten

De klanttabel van de winkel heeft drie kolommen en de besteltabel heeft er vier. Met JOIN ... ON retourneert SELECT * ze alle zeven, inclusief beide exemplaren van customer_id. Je kunt ook alleen de velden selecteren die het rapport nodig heeft:

SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Het resultaat is (Ana; 101; 22,00), (Ana; 102; 25,50) en (Ben; 103; 60,00). De sleutelkolommen zorgen voor de koppeling zonder dat ze in de geselecteerde uitvoer hoeven te verschijnen. Minder kolommen selecteren verandert de breedte van het resultaat, niet welke invoerparen overeenkomen.

Geef tabellen korte, duidelijke namen

In FROM customers c is c een tabelalias. Verwijs naar kolommen van die tabel als c.name en c.customer_id. De expressie c.name is een gekwalificeerde kolomnaam: het deel vóór de punt geeft aan welke tabelrol de waarde levert.

Beide gekoppelde tabellen bevatten customer_id. Als je bij de vorm met ON alleen SELECT customer_id schrijft, kan de database niet kiezen tussen de twee kolommen en ontstaat een fout door een dubbelzinnige kolomverwijzing. Schrijf c.customer_id of o.customer_id, afhankelijk van de waarde die je nodig hebt. Door ook andere geselecteerde kolommen te kwalificeren, maak je hun herkomst gemakkelijk zichtbaar.

AS vóór een tabelalias is optioneel in PostgreSQL, SQLite, MySQL en SQL Server. Oracle accepteert het niet, dus FROM customers c is een bruikbare algemene vorm. Nadat je een tabel een alias hebt gegeven, gebruik je die alias consequent.

Lees de logische volgorde van de query

FROM en de bijbehorende joins bouwen de gecombineerde rijen op. WHERE filtert die rijen. SELECT kiest de uitvoerkolommen en ORDER BY sorteert het resultaat. Een filter kan een gekoppelde kolom gebruiken, ook als die kolom niet is geselecteerd. Zo kan WHERE c.city = 'Denver' een rapport filteren dat alleen namen en ordertotalen toont.

Zonder ORDER BY garandeert de database geen bepaalde rijvolgorde. Een query die vandaag Ana eerst toont, kan later een andere rij eerst tonen; sorteren is een aparte instructie naast het koppelen.

Gebruik een inner join als een resultaatrij een overeenkomst in beide tabellen nodig heeft. JOIN en INNER JOIN betekenen hetzelfde. Geen van beide behoudt een rij zonder overeenkomst.

Bouw de query in drie delen op

Noem eerst de tabellen en hun rollen. Geef daarna de relatie aan in ON. Kies ten slotte de kolommen die je wilt tonen.

SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Lees de voorwaarde als “de klantverwijzing van de bestelling is gelijk aan de identificatiewaarde van deze klant”. Voor bestelling 101 is de verwijzing 1, dus die wordt aan Ana gekoppeld. Bestelling 102 heeft ook verwijzing 1 en bestelling 103 heeft verwijzing 2. Het volledige resultaat is:

Ana verschijnt tweemaal omdat ze twee overeenkomende bestellingen heeft. Chloe en Dev hebben er geen en verschijnen dus niet. Gastbestelling 104 heeft een klantverwijzing met NULL, die aan geen enkele gelijkheid met een klantsleutel voldoet.

Filter en sorteer de gecombineerde rijen

Om alleen klanten uit Denver te tonen, plaats je WHERE c.city = 'Denver' na de join en vóór ORDER BY. Het resultaat wordt (Ana; 101; 22,00) en (Ana; 102; 25,50). Chloe woont ook in Denver, maar de inner join heeft voor haar geen rij opgeleverd.

Bij een inner join levert dit filter in ON dezelfde paren op. Een leesbare afspraak is om koppelingsvoorwaarden voor tabellen in ON en rapportfilters in WHERE te zetten. Deze gelijkwaardigheid geldt niet voor outer joins.

Je kunt sorteren op een gekoppelde kolom die niet in de geselecteerde uitvoer staat. Je kunt de joinsleutels ook uit de uitvoer weglaten: de bovenstaande query koppelt op customer_id en toont een naam, een bestelnummer en een totaal. De rol van een kolom bij het koppelen staat los van haar rol in het rapport.

Vergelijk elk deel van een sleutel

Sommige records worden door een paar kolommen geïdentificeerd. Een verzendregel die naar een orderregel verwijst, kan zowel de identificatiewaarde van de bestelling als die van het product nodig hebben:

SELECT s.order_id, s.product_id, oi.quantity
FROM shipment_lines s
JOIN order_items oi
  ON s.order_id = oi.order_id
 AND s.product_id = oi.product_id;

Beide vergelijkingen moeten waar zijn voor een overeenkomst. Als je alleen order_id vergelijkt, wordt elke verzendregel aan elke productregel in die bestelling gekoppeld. De query kan dan succesvol worden uitgevoerd en toch de verkeerde productaantallen beschrijven.

Verander de volgorde zonder de betekenis te veranderen

FROM orders o JOIN customers c ON c.customer_id = o.customer_id geeft dezelfde klant-bestelparen. Ook als je de twee kanten van = omdraait, blijft de gelijkheid behouden. SELECT * kan de kolommen na het omwisselen van tabellen anders ordenen en de rijvolgorde blijft onbepaald tenzij je sorteert.

Een veel ingrijpendere wijziging is ON o.order_id = c.customer_id. Beide kolommen bevatten getallen, dus dit is geldige SQL, maar ze identificeren verschillende dingen. In de winkelgegevens levert dit nul rijen op: bestel-ID's 101–104 zijn nooit gelijk aan klant-ID's 1–4. Controleer de relatie in het schema, ook als de database geen syntaxisfout meldt.

ON maakt een voorwaarde expliciet, maar bestaande SQL kan kortere of oudere vormen gebruiken. Lees elke vorm door je af te vragen welke paren hij behoudt. Sommige vormen veranderen ook hoe gedeelde kolommen in de uitvoer verschijnen.

USING noemt de gedeelde sleutel

Als de sleutelkolommen dezelfde naam hebben, gebruikt deze query een verkorte gelijkheid:

SELECT c.name, o.order_id
FROM customers c
JOIN orders o USING (customer_id)
ORDER BY o.order_id;

De query retourneert (Ana, 101), (Ana, 102) en (Ben, 103). USING (customer_id) betekent dat de twee kolommen customer_id worden vergeleken. Met SELECT * retourneert de query zes kolommen en toont ze de gedeelde sleutel eenmaal; JOIN ... ON bevat beide invoersleutelkolommen.

Bij een samengestelde sleutel noem je beide namen: USING (order_id, product_id). Elke genoemde kolom moet onder die naam in beide invoertabellen bestaan.

PostgreSQL, SQLite, MySQL en Oracle ondersteunen USING; SQL Server doet dat niet. In Oracle moet een kolom die in USING wordt genoemd ongekwalificeerd zijn als je ernaar verwijst. Schrijf dus SELECT customer_id, niet SELECT c.customer_id. Andere kolommen kunnen nog steeds hun tabelalias gebruiken.

NATURAL JOIN kiest kolommen voor je

Een NATURAL JOIN vergelijkt alle kolomnamen die de invoertabellen delen, combineert die gelijkheidstests met AND en toont de gedeelde kolommen eenmaal. Deze vorm wordt ondersteund door PostgreSQL, SQLite, MySQL en Oracle, maar niet door SQL Server.

Die automatische keuze kan een query ongemerkt veranderen. Klanten en bestellingen delen nu alleen customer_id. Als je een kolom city aan bestellingen toevoegt, vereist een natural join dat zowel klant-ID als plaats overeenkomt. Een bezorgplaats die verschilt van de plaats van de klant kan dan een eerder overeenkomende bestelling verwijderen.

Klanten en producten hebben allebei name, maar de ene kolom benoemt een persoon en de andere een product. Hun natural join zou die niet-gerelateerde namen vergelijken en retourneert in de winkelgegevens geen rijen. Zonder gedeelde kolomnamen retourneert een natural join daarentegen elke combinatie. Gebruik ON of een bewust gekozen USING-lijst om de koppelingsregel zichtbaar te houden.

Lees oudere kommajoins

SELECT c.name, o.order_id
FROM customers c, orders o
WHERE o.customer_id = c.customer_id;

Dit retourneert dezelfde drie paren als de inner join. De voorwaarde staat nu in WHERE. Als je die vergeet, worden alle vier klanten aan alle vier bestellingen gekoppeld, wat 16 rijen oplevert. Bij JOIN ... ON staat elke tabel naast haar eigen voorwaarde, waardoor langere query's gemakkelijker te controleren zijn.

Koppel een waarde aan een bereik

Een equijoin gebruikt gelijkheid. Een niet-equijoin gebruikt een andere vergelijking, zoals een bereiktest:

SELECT p.name, b.band
FROM products p
JOIN price_bands b
  ON p.price BETWEEN b.min_price AND b.max_price;

De klassen zijn Budget 0,00–4,99, Standard 5,00–19,99 en Premium 20,00–999,99. Mug voor 8,00 en Stapler voor 12,00 vallen in Standard; Pen voor 1,50 en Notebook voor 4,25 vallen in Budget. Premium heeft geen overeenkomend product en levert geen rij op.

BETWEEN omvat beide grenspunten. Een nieuwe klasse Gift van 8,00 tot 15,00 zou Mug en Stapler elk een tweede overeenkomst geven. Door het gat tussen 4,99 en 5,00 zou een prijs van 4,995 geen overeenkomst hebben. Bereiken bepalen het resultaat net als sleutels: een waarde kan geen, één of meerdere overeenkomsten hebben.

Een rapport dat vraagt om “alle klanten, inclusief klanten zonder bestellingen” heeft een left outer join nodig. LEFT JOIN behoudt elke linkerrij. De join koppelt elke overeenkomende rechterrij, of levert één rij met NULL-waarden als er geen overeenkomst is. Het woord OUTER is optioneel.

Volg de behouden tabel

SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Ana levert nog steeds twee rijen op. Een left join brengt de bestellingen van een klant niet terug tot één. Chloe en Dev leveren elk precies één rij op omdat geen van beiden een overeenkomst heeft. In totaal zijn er vijf rijen: drie overeenkomende paren en twee klanten zonder overeenkomst.

De join retourneert vóór latere filters minstens evenveel rijen als de linkerinvoer. Tel voor elke linkerrij de overeenkomsten; als die er geen heeft, tel je één met NULL-waarden aangevulde rij. Dit verklaart waarom vier klanten vijf rijen kunnen opleveren.

Gastbestelling 104 ontbreekt. Als je elke klant behoudt, behoud je niet ook de bestellingen zonder overeenkomst. Om elke bestelling te behouden, begin je met orders o LEFT JOIN customers c ON c.customer_id = o.customer_id. Als je vervolgens o.order_id, c.name selecteert, krijg je (101, Ana), (102, Ana), (103, Ben) en (104, NULL). Chloe en Dev verschijnen niet meer.

Vind rijen zonder tegenhanger

Een left anti-join behoudt alleen rijen waarvoor geen overeenkomst is gevonden:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

De left join maakt eerst alle vijf rijen. WHERE behoudt daarna Chloe en Dev, van wie de bestelsleutels NULL zijn. Hun ontbrekende tegenhanger is precies waar de query naar zoekt.

Kies een kolom aan de rechterkant die bij een echte overeenkomst geen NULL kan zijn. De primaire sleutel van een bestelling is een goede test. Als je een optionele coupon_code test, behoud je ook echte overeenkomende bestellingen zonder kortingsbon. Dat zou dus niet “geen bestelling” betekenen. Gebruik IS NULL: = NULL wordt als onbekend beoordeeld en behoudt in WHERE geen rijen.

Hetzelfde patroon vindt onverkochte producten:

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL;

Dit retourneert (Stapler), het enige product zonder orderregel. Een echte overeenkomende regel heeft hier noodzakelijkerwijs een bestel-ID, dus de test vindt de ontbrekende regel.

Interpreteer een ontbrekende waarde voordat je die vervangt

Een met NULL-waarden aangevulde outer-joinrij betekent dat er geen overeenkomende tegenhanger is. Een opgeslagen NULL in een echte bestelling kan daarentegen betekenen dat een veld niet is ingevuld. Dat zijn verschillende situaties, ook al verschijnen beide als NULL.

COALESCE(o.total, 0) kiest het eerste argument dat geen NULL is. Voor Chloe en Dev zou dit 0 tonen. Dat kan nuttig zijn voor een rapport over bestedingen, maar een nul in een kolom met het opschrift “ordertotaal” kan een echte bestelling met een waarde van nul suggereren. Zorg dat het opschrift en de betekenis van het rapport bij elkaar passen.

Soms staan de benodigde rijen rechts van een join. Soms moeten beide invoertabellen behouden blijven om ontbrekende records te kunnen vergelijken. Right en full outer joins maken deze keuzes expliciet.

Herschrijf een right join als een left join

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

Dit behoudt elke bestelling: (Ana, 101), (Ana, 102), (Ben, 103) en (NULL, 104). De rechtertabel is orders, dus de gastbestelling blijft behouden. Chloe en Dev zijn linkerrijen zonder overeenkomst en blijven niet behouden.

Als je de invoertabellen omwisselt, krijg je de gelijkwaardige vorm orders o LEFT JOIN customers c ON o.customer_id = c.customer_id. Met dezelfde expliciete selectielijst krijg je dezelfde waarden. Bij SELECT * kan de standaardkolomvolgorde veranderen. Als je in een rapport overal left joins gebruikt, is de kant die behouden moet blijven vaak gemakkelijker te volgen.

Behoud ontbrekende rijen uit beide invoertabellen

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

Het resultaat bevat (Ana, 101), (Ana, 102), (Ben, 103), (Chloe, NULL), (Dev, NULL) en (NULL, 104). Dit zijn drie overeenkomende paren, twee klanten zonder overeenkomst en één bestelling zonder overeenkomst: zes rijen. De klantkolommen zijn NULL voor gastbestelling 104; de bestelkolommen zijn NULL voor Chloe en Dev.

Een full join is nuttig voor een aansluitcontrole, zoals het controleren van een voorraadlijst van een magazijn tegen een fysieke telling. Overeenkomende sleutels verschijnen samen; sleutels die slechts aan één kant voorkomen, blijven zichtbaar. Als je in de winkelquery WHERE c.customer_id IS NULL OR o.order_id IS NULL toevoegt, blijven alleen Chloe, Dev en bestelling 104 over.

Als je twee lijsten met hetzelfde soort sleutel aansluit, kan elk van de invoersleutels in de uitvoer NULL zijn. COALESCE(w.sku, k.sku) AS sku selecteert de beschikbare SKU uit de magazijnalias w of de tellijstalias k. Met een gecombineerde sleutelkolom kan het rapport records identificeren die in een van beide lijsten ontbreken.

Controleer de ondersteuning van de syntaxis

PostgreSQL, SQL Server en Oracle ondersteunen right en full outer joins. SQLite ondersteunt beide vanaf versie 3.39.0, die in 2022 uitkwam. Oudere SQLite-versies accepteren ze niet. MySQL ondersteunt right joins, maar heeft geen FULL OUTER JOIN; een omweg combineert de resultaten van een left en een right join met UNION.

Oudere Oracle-SQL kan in plaats daarvan de optionele zijde markeren met (+) in WHERE. Zo behoudt WHERE c.customer_id = o.customer_id(+) de klanten en vult het ontbrekende bestelkolommen met NULL, net als een left join vanaf klanten. De markering hoort bij de kant die mag ontbreken. Gebruik bij het schrijven van nieuwe query's de benoemde joinvormen LEFT, RIGHT en FULL.

Een outer join scheidt twee beslissingen: welke paren als overeenkomsten tellen en welke voltooide resultaatrijen het rapport behoudt. ON neemt de eerste beslissing. WHERE neemt de tweede, nadat rijen zonder overeenkomst NULL-waarden hebben gekregen.

Behoud iedereen, maar beperk de overeenkomende bestellingen

Stel dat een rapport alle klanten nodig heeft en alleen hun bestellingen vanaf 10 maart:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-03-10';

De datumtest maakt deel uit van het koppelen. Ana's bestelling 101 is te vroeg, maar bestelling 102 voldoet. Bens bestelling 103 voldoet ook. Chloe en Dev hebben geen tegenhanger die voldoet, dus krijgen ze elk een met NULL-waarden aangevulde rij. Het resultaat is (Ana, 102), (Ben, 103), (Chloe, NULL) en (Dev, NULL).

Filter de rijen na de join

Verplaats dezelfde datumtest naar WHERE:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-03-10';

Nu maakt de join eerst vijf rijen, inclusief de met NULL-waarden aangevulde rijen voor Chloe en Dev. De datumvergelijking is onbekend voor die NULL-waarden, dus WHERE verwijdert ze. Ook Ana's vroege bestelling wordt verwijderd. Alleen (Ana, 102) en (Ben, 103) blijven over.

De versie met WHERE levert dezelfde overeenkomende rijen op als een inner join met dat datumfilter.

Filter de behouden tabel bewust

Ook een voorwaarde op de linkerkant in ON kan geen linkerrijen verwijderen. Neem ON o.customer_id = c.customer_id AND c.city = 'Denver'. Ana kan met beide bestellingen overeenkomen, Chloe heeft er geen en Ben en Dev voldoen bij geen enkele mogelijke overeenkomst aan het plaatsgedeelte. Alle vier klanten blijven behouden, met NULL in de bestelkolommen voor Ben, Chloe en Dev.

Om alleen klanten uit Denver op te vragen, zet je WHERE c.city = 'Denver' na de gewone sleutelvoorwaarde. Het resultaat is (Ana, 101), (Ana, 102) en (Chloe, NULL). Chloe blijft behouden omdat het filter haar eigen woonplaats test, niet een ontbrekend bestelveld.

De anti-jointest hoort na de join omdat deze het met NULL-waarden aangevulde resultaat onderzoekt. Inner joins behouden geen rijen zonder overeenkomst, dus als je een filter tussen ON en WHERE verplaatst, veranderen hun overeenkomende paren niet. Lees bij het controleren van een outer join elke WHERE-test op de optionele zijde en controleer wat die met een met NULL-waarden aangevulde rij doet.

De leidinggevende van een werknemer is ook een werknemer. Een selfjoin leest dezelfde tabel onder verschillende aliassen, zodat de ene rij de werknemer en de andere de leidinggevende kan voorstellen. Een selfjoin gebruikt gewone joinsyntaxis; er is geen trefwoord SELF JOIN.

Benoem de rollen vóór de kolommen

De werknemersgegevens zijn:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

e levert de werknemersrij. De manager_id daarvan verwijst naar de emp_id van de leidinggevende in rol m. Hassan verwijst naar 1 en wordt dus aan Grace gekoppeld; ook Iris wordt aan Grace gekoppeld. Jamal verwijst naar 2 en wordt aan Hassan gekoppeld. Het resultaat is (Hassan, Grace), (Iris, Grace) en (Jamal, Hassan).

Grace heeft geen leidinggevende. Haar manager_id is NULL, dus de inner join sluit haar uit. Als je JOIN vervangt door LEFT JOIN, wordt (Grace, NULL) toegevoegd en krijg je vier rijen. Door verschillende aliassen kan de database de rollen onderscheiden, ook al komen beide invoertabellen uit dezelfde opgeslagen tabel.

Stel de vraag in de andere richting

Om mensen te vinden die aan niemand leidinggeven, begin je bij de rol van leidinggevende en zoek je naar een ontbrekende werknemer:

SELECT m.name
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.emp_id
WHERE e.emp_id IS NULL;

Dit retourneert (Iris) en (Jamal). Grace heeft een overeenkomst met Hassan en Iris; Hassan heeft een overeenkomst met Jamal. Iris en Jamal hebben geen directe ondergeschikten. Door na de join de werknemerssleutel te testen, vind je hun ontbrekende tegenhanger.

Een derde alias volgt nog één niveau. Als je hun namen selecteert, retourneert employees e JOIN employees m ON e.manager_id = m.emp_id JOIN employees g ON m.manager_id = g.emp_id (Jamal, Hassan, Grace). Elke extra rol volgt nog één schakel. Een vaste query bereikt een vast aantal niveaus; een hiërarchie van willekeurige diepte heeft een recursieve query nodig.

Koppel collega's eenmaal aan elkaar

Dezelfde tabel kan ook paren collega's opleveren:

SELECT a.name, b.name
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.emp_id < b.emp_id;

Hassan en Iris hebben leidinggevende 1 gemeen. De kleiner-dantest sluit uit dat een persoon aan zichzelf wordt gekoppeld en behoudt slechts één volgorde van elk paar. Het resultaat is (Hassan, Iris).

Met alleen de gelijkheid van leidinggevenden heeft het resultaat vijf rijen: Hassan–Hassan, Hassan–Iris, Iris–Hassan, Iris–Iris en Jamal–Jamal. De NULL-sleutel van Graces leidinggevende komt met niets overeen, ook niet met een andere NULL. Als je < vervangt door <>, sluit je koppelingen aan zichzelf uit, maar behoud je zowel (Hassan, Iris) als (Iris, Hassan). Kies < als de vraag elk ongeordend paar eenmaal vereist.

Een cross join maakt elk paar rijen, zonder koppelingsvoorwaarde. Dit is nuttig als de vraag om mogelijkheden vraagt, zoals elke combinatie van maat en kleur die een winkel op voorraad kan hebben.

Maak de combinatielijst

sizes bevat S, M en L. colors bevat Red en Blue.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

De zes paren zijn (S, Red), (S, Blue), (M, Red), (M, Blue), (L, Red) en (L, Blue). Elke maat verschijnt naast beide kleuren. Dit zijn de resultaatrijen; hun weergavevolgorde is zonder ORDER BY niet gegarandeerd.

Vermenigvuldig bij twee invoertabellen hun aantallen rijen. Drie maten en twee kleuren geven zes rijen. Met een derde tabel die vier opties bevat, heeft het resultaat 24 rijen. Als een invoertabel leeg is, kan er geen volledige combinatie worden gemaakt en heeft het resultaat nul rijen.

Begin met een volledig rapportageraster

Verkooprecords tonen alleen combinaties die zijn voorgekomen. Om elke winkel in elke maand te tonen, maak je eerst een raster met stores CROSS JOIN months. Gebruik daarna een left join om overeenkomende verkooprecords te koppelen:

SELECT s.store_id, m.month, x.total
FROM stores s
CROSS JOIN months m
LEFT JOIN sales x
  ON x.store_id = s.store_id
 AND x.month = m.month;

Het raster behoudt een winkel-maandpaar ook als er geen verkoop is, met NULL in x.total. Beide sleuteldelen zijn van belang: als je alleen de winkel vergelijkt, koppel je ook de verkopen van die winkel uit andere maanden. De cross join bepaalt welke combinaties het rapport moet tonen en de left join levert de beschikbare gegevens daarover.

Herken een onbedoelde cross join

FROM customers, orders zonder koppelingsvoorwaarde maakt 16 paren van de vier klanten en vier bestellingen. Ana wordt gekoppeld aan haar eigen bestellingen, Bens bestelling en de gastbestelling. Voor elke klant gebeurt hetzelfde. Dat is alleen nuttig als elke klant-bestelcombinatie ook echt gewenst is.

Een groot aantal rijen is een aanwijzing, maar bekijk ook de koppelingen. Met 1.000 klanten en 1.000 producten maakt een bewuste cross join 1.000.000 rijen. Een join met een verkeerde maar selectieve voorwaarde kan ook een kleiner, onjuist resultaat opleveren. Vergelijk het aantal en de betekenis van elk paar met de vraag.

Herken gelijkwaardige vormen

CROSS JOIN, een kommajoin zonder koppelingsvoorwaarde en een inner join met ON 1 = 1 behouden allemaal elk paar. SQLite en MySQL accepteren ook een gewone JOIN zonder ON en behandelen die als een cross join; PostgreSQL vereist ON of USING bij een gewone join. Schrijf CROSS JOIN als elke combinatie bewust is bedoeld.

DISTINCT verwijdert identieke geselecteerde rijen, geen onjuiste relaties. Als een onbedoelde cross join Ana aan Bens bestelling koppelt, blijven die waarden als een unieke rij bestaan. Herstel de ontbrekende voorwaarde in plaats van een deel van de herhaalde uitvoer te verbergen.

Een student kan meerdere cursussen volgen en een cursus kan meerdere studenten hebben. Een koppeltabel legt deze veel-op-veelkoppelingen als afzonderlijke paren vast. De tabel wordt ook een brug-, verbindings- of associatietabel genoemd.

Lees één koppeling per rij

De voorbeeldgegevens bevatten studenten 1 Mia, 2 Noah en 3 Omar; cursussen 10 Algebra, 20 Biology en 30 Chemistry; en inschrijvingsparen (1, 10), (1, 20) en (2, 10).

Student 1 verschijnt tweemaal omdat Mia twee cursussen volgt. Cursus 10 verschijnt tweemaal omdat Algebra twee studenten heeft. Geen van beide kolommen hoeft afzonderlijk uniek te zijn. Het paar kan een samengestelde primaire sleutel vormen, PRIMARY KEY (student_id, course_id), die voorkomt dat dezelfde student tweemaal voor dezelfde cursus wordt ingeschreven.

Een koppeling kan eigen gegevens hebben. enrolled_on hoort bij de inschrijving omdat het beschrijft wanneer een bepaalde student aan een bepaalde cursus begon. Evenzo beschrijft order_items.quantity hoeveel van een bepaald product bij een bepaalde bestelling hoort.

Ga met twee joins via de koppeltabel

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id;

De eerste voorwaarde koppelt de inschrijvingsrijen van elke student. De tweede vervangt de cursusverwijzing van elke inschrijving door gegevens uit de cursustabel. Het volledige resultaat is (Mia, Algebra), (Mia, Biology) en (Noah, Algebra). Elke rij staat voor één inschrijving.

Geen van beide eindtabellen bevat een sleutel van student naar cursus. Als je s.student_id = c.course_id vergelijkt, vergelijk je niet-gerelateerde identificatiewaarden en sla je de inschrijvingsgegevens over. De koppeltabel levert de relatie.

Hetzelfde pad werkt in beide richtingen. Voeg WHERE c.title = 'Algebra' toe om (Mia, Algebra) en (Noah, Algebra) te krijgen. Voeg in plaats daarvan WHERE s.name = 'Mia' toe om (Mia, Algebra) en (Mia, Biology) te krijgen. De koppelingen blijven hetzelfde; het filter kiest welke koppelingen de vraag beantwoorden.

Neem personen of cursussen zonder koppelingen mee

Begin aan de kant die behouden moet blijven. students s LEFT JOIN enrollments e ON e.student_id = s.student_id behoudt Omar met NULL in de inschrijvingskolommen. Om cursustitels te tonen en hem te behouden, moet ook de latere join van inschrijvingen naar cursussen de ontbrekende inschrijving behouden.

Om cursussen te vinden die niemand volgt, gebruik je de cursuskant en een anti-join:

SELECT c.title
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
WHERE e.student_id IS NULL;

Dit retourneert (Chemistry). Bij een echte overeenkomende inschrijving maakt student_id deel uit van de primaire sleutel en kan het geen NULL zijn. De test herkent dus de ontbrekende koppeling.

Sla één relatie per koppelrij op. Een tekstlijst zoals “10,20” in één studentenveld kan bij een gelijkheidsvergelijking niet overeenkomen met afzonderlijke cursus-ID's 10 en 20. Dankzij afzonderlijke paren kan elke joinvoorwaarde één verwijzing met één sleutel vergelijken.

Een langere join volgt dezelfde regel als een join tussen twee tabellen: koppel de volgende tabel met een zinvolle voorwaarde. Volg voordat je de query schrijft de sleutels en bepaal wat één resultaatrij moet vertegenwoordigen.

Voeg de gedetailleerde winkelgegevens toe

De orderregels (order_id, product_id, quantity) zijn (101, 1, 2), (101, 2, 4), (102, 3, 6), (103, 1, 6), (103, 2, 8) en (104, 2, 10). Elke regel koppelt een bestelling aan een product en legt een aantal vast.

Volg voor de vraag “Wat heeft elke klant gekocht?” customers → orders → order_items → products. De sleutels langs het pad zijn klant-ID, bestel-ID en product-ID. Vier tabellen langs deze keten vereisen drie koppelingen.

Koppel telkens één nieuwe tabel

SELECT c.name, o.order_id, p.name AS product,
       oi.quantity, oi.quantity * p.price AS line_total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_id;

Elke ON verwijst naar de nieuw geïntroduceerde tabel en een tabel die al beschikbaar is. Producten worden via orderregels gekoppeld, niet rechtstreeks via klanten. Het resultaat is:

De eerste join sluit gastbestelling 104 uit omdat de klantverwijzing NULL is. De overige drie bestellingen bevatten vijf regels, dus het eindresultaat heeft vijf rijen. Voor bestelling 101 kosten twee mokken 16,00 en vier pennen 6,00, samen gelijk aan het ordertotaal van 22,00.

Weet wat een rij telt

Eén resultaatrij vertegenwoordigt een orderregel. Ana en bestelling 101 verschijnen tweemaal omdat die bestelling twee producten heeft. Herhaalde klantnamen zijn dus te verwachten. Als je deze rijen telt, tel je regels, geen unieke klanten of bestellingen.

Een filter mag naar elke gekoppelde tabel verwijzen. Als je WHERE p.name = 'Pen' vóór ORDER BY toevoegt, blijven (Ana; 101; Pen; 4; 6,00) en (Ben; 103; Pen; 8; 12,00) over. Geen productfilter kan de gastbestelling terughalen die al bij de klantkoppeling is uitgesloten.

Houd elke koppeling zichtbaar

Bij een keten van inner joins kan de geschreven tabelvolgorde veranderen zonder de overeenkomende combinaties te veranderen, mits elke voorwaarde alleen verwijst naar tabellen die al zijn geïntroduceerd. Beginnen bij het onderwerp van het rapport en de sleutels volgen is meestal het gemakkelijkst te lezen.

Een ontbrekende voorwaarde kan een tussenresultaat vermenigvuldigen in SQLite of MySQL, die een gewone JOIN zonder ON accepteren. PostgreSQL en SQL Server wijzen die syntaxis af. Ook een ontbrekende voorwaarde in een kommaquery kan bij die stap elke combinatie opleveren.

Soms vervult één tabel twee rollen. Een vluchtrapport kan airports als dep koppelen op dep.code = f.from_code en nogmaals als arr op arr.code = f.to_code. Verschillende aliassen geven de vertrek- en aankomstgegevens afzonderlijke plaatsen in het resultaat.

Een left join behoudt rijen bij één stap. Latere joins en filters werken nog steeds op het resultaat van die stap. Om een ouder zonder overeenkomst op een langer pad te behouden, volg je wat er bij elke stap met de met NULL-waarden aangevulde kolommen gebeurt.

Bekijk waar een behouden rij verdwijnt

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id;

De eerste stap maakt vijf rijen: Ana's twee bestellingen, Bens bestelling en elk één met NULL-waarden aangevulde rij voor Chloe en Dev. Bij de volgende stap zijn de waarden van o.order_id voor Chloe en Dev NULL. Ze kunnen niet voldoen aan de gelijkheid met een orderregelsleutel, dus verwijdert de inner join ze. De query retourneert vijf orderregelrijen en verliest beide klanten zonder bestellingen.

Behoud het optionele pad

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.order_id;

Nu behoudt de tweede join ook die met NULL-waarden aangevulde rijen. Het resultaat bevat (Ana, 101, 1), (Ana, 101, 2), (Ana, 102, 3), (Ben, 103, 1), (Ben, 103, 2), (Chloe, NULL, NULL) en (Dev, NULL, NULL): zeven rijen.

Als latere joins afhangen van kolommen van een optionele tabel, moeten het meestal left joins blijven om ontbrekende tegenhangers te behouden. Een latere inner join op kolommen van een verplichte tabel heeft een andere rol, dus onderzoek de daadwerkelijke afhankelijkheid in elke voorwaarde.

Volg dezelfde regel via een koppeltabel

SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
LEFT JOIN courses c ON c.course_id = e.course_id;

Dit retourneert (Mia, Algebra), (Mia, Biology), (Noah, Algebra) en (Omar, NULL). Als je van de laatste join een inner join maakt, verdwijnt Omar omdat zijn ontbrekende inschrijving niet met een cursus kan overeenkomen.

Haakjes bieden een andere manier om de groepering uit te drukken: students s LEFT JOIN (enrollments e JOIN courses c ON c.course_id = e.course_id) ON e.student_id = s.student_id. De overeenkomsten tussen inschrijvingen en cursussen vormen de optionele groep; de outer join koppelt die groep aan elke student en behoudt zo Omar.

Controleer latere filters en de joinrichting

Als je WHERE c.title = 'Algebra' toevoegt aan de studentenquery met alleen left joins, blijven alleen Mia en Noah bij Algebra over. De vergelijking is onbekend voor Omars cursustitel met NULL. Dit beantwoordt “Wie volgt Algebra?”, maar niet meer “Toon alle studenten en hun cursussen.” Een IS NULL-test gedraagt zich anders omdat deze bewust een ontbrekende waarde accepteert.

Een latere right join verandert de behouden zijde opnieuw: hij behoudt de nieuw toegevoegde rechtertabel en eerdere rijen zonder overeenkomst kunnen verdwijnen. Zet de tabel die behouden moet blijven voorop en koppel optionele paden met left joins als dat het rapport duidelijk weergeeft.

Bouw en bekijk de query telkens één join tegelijk. Noteer zowel het aantal rijen als de sleutels die overblijven. Een dalend aantal kan weggevallen rijen zonder overeenkomst onthullen; een onverwachte stijging kan een ontbrekend sleutelonderdeel onthullen. Sommige aantallen blijven gelijk als rijen zowel wegvallen als worden vermenigvuldigd, dus bekijk ook representatieve sleutels.

Een join kan correct zijn terwijl een aggregatie over het resultaat onjuist is. De belangrijkste vraag is wat één rij na de join vertegenwoordigt. Een waarde die eenmaal per bestelling is opgeslagen, kan meerdere keren in een lijst met orderregels verschijnen.

Volg een herhaald ordertotaal

Ana heeft bestelling 101 voor 22,00 en bestelling 102 voor 25,50. De twee bestellingen optellen geeft 47,50. Bestelling 101 heeft twee regels, dus de join met order_items herhaalt 22,00 tweemaal; bestelling 102 heeft één regel. Als je de herhaalde waarden optelt, krijg je 69,50.

SELECT c.customer_id, c.name, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

GROUP BY verzamelt de rijen per klant en SUM telt de waarden van die groep op. Deze query retourneert (1; Ana; 47,50) en (2; Ben; 60,00). Als je de orderregeljoin vóór het groeperen toevoegt, wordt Ana's totaal te hoog: 69,50, en dat van Ben 120,00. De totalen van de twee klanten samen worden 189,50 in plaats van 107,50.

Over alle vier bestellingen, inclusief gastbestelling 104, zorgt een rechtstreekse join van bestellingen met hun regels ervoor dat SUM(o.total) 204,50 retourneert in plaats van 122,50. Deze rijvermenigvuldiging ontstaat door de een-op-veelkoppeling, niet door een syntaxisfout.

Sommeer gegevens op hun opgeslagen niveau

Voor ordertotalen tel je bestellingen op zonder de detailtabel toe te voegen. Voor een totaal van regelbedragen bereken je de waarde van elke regel zelf:

SELECT SUM(oi.quantity * p.price) AS sales
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id;

De zes regelbedragen zijn 16,00, 6,00, 25,50, 48,00, 12,00 en 15,00. Samen zijn ze 122,50, het totaal van alle vier bestellingen. Elk product van aantal en prijs hoort bij één regel en wordt dus eenmaal opgeteld.

Tel echte overeenkomsten

Nadat klanten aan bestellingen en regels zijn gekoppeld, neemt Ana drie rijen in. COUNT(*) telt die regels; COUNT(DISTINCT o.order_id) telt haar twee unieke bestellingen.

Na alleen een left join van klanten naar bestellingen telt COUNT(*) één met NULL-waarden aangevulde rij voor Chloe en één voor Dev. Tel in plaats daarvan de optionele bestelsleutel:

SELECT c.customer_id, c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

Dit retourneert (1, Ana, 2), (2, Ben, 1), (3, Chloe, 0) en (4, Dev, 0). COUNT(column) negeert NULL-waarden; COUNT(*) telt rijen.

Twee veel-kanten vermenigvuldigen elkaar

Als een klant drie bestellingen en twee ondersteuningsverzoeken heeft, levert het onafhankelijk koppelen van beide kindtabellen aan die klant zes rijen op. Elke bestelling wordt aan elk verzoek gekoppeld. Gewone tellingen van beide kindsleutels worden zes. Tel unieke kindsleutels om drie en twee te krijgen, of tel de kindtabellen afzonderlijk op.

DISTINCT c.customer_id, c.name is nuttig voor een unieke klantenlijst. Door de sleutel te behouden voorkom je dat verschillende klanten met dezelfde naam worden samengevoegd. Het herstelt geen ontbrekende joinvoorwaarde. Ook SUM(DISTINCT o.total) is onbetrouwbaar: het voegt twee afzonderlijke bestellingen samen die toevallig hetzelfde bedrag hebben.

Een query kan worden uitgevoerd en toch de verkeerde relaties beschrijven. Voorspel voordat je een joinresultaat gebruikt wat behouden moet blijven, hoe vaak elke sleutel moet verschijnen en wat één resultaatrij moet betekenen.

Voorspel het aantal rijen

Als de klantverwijzing van een bestelling overeenkomt met een unieke klantsleutel, heeft elke bestelling maximaal één overeenkomende klant. Een inner join retourneert daarom maximaal één rij per bestelling. In de winkelgegevens hebben drie van de vier bestellingen een klant, dus retourneert de inner join tussen klanten en bestellingen drie rijen.

Vanaf de klantkant retourneert een left join de drie overeenkomende paren plus één rij voor Chloe en één voor Dev: vijf rijen. Een cross join van de vier klanten en vier bestellingen geeft 16. Deze voorspellingen bieden verschillende controles voor verschillende jointypen; alleen het aantal tabellen bepaalt het aantal rijen niet.

Onderzoek de vorm van een onjuist resultaat

Als je alleen store_id vergelijkt terwijl records door (store_id, month) worden geïdentificeerd, meng je verschillende maanden. Vergelijk elk onderdeel met AND. Een query die toevallig werkt zolang een winkel één maand heeft, kan misgaan zodra een tweede maand wordt ingeladen.

Onderscheid ontbrekende waarden van verkeerde sleutels

Bij gewone gelijkheid komen NULL-sleutels niet overeen, ook niet NULL met NULL. Als je bestellingen op customer_id aan zichzelf koppelt, krijg je vier paren uit Ana's twee bestellingen en één uit Bens bestelling, samen vijf rijen. De gastbestelling levert er geen op. Een outer join is nodig als een rij met een ontbrekende verwijzing behouden moet blijven.

Dubbele opzoekwaarden vermenigvuldigen alleen de overeenkomsten voor die waarden. Als een landcode tweemaal in een opzoektabel voorkomt, krijgt elke klant met die code twee bijbehorende rijen. Codes die eenmaal voorkomen, geven nog steeds één tegenhanger. Onderzoek een opzoektabel die uniek zou moeten zijn met:

SELECT code, COUNT(*) AS copies
FROM countries
GROUP BY code
HAVING COUNT(*) > 1;

GROUP BY verzamelt de rijen van elke code; HAVING behoudt groepen waarvan het aantal groter is dan één. Als je de hele opzoektabel tweemaal inlaadt, verdubbelen de rijen van elke overeenkomende klant.

Controleer ook sleuteltypen en -notaties. De tekst “0042” en het getal 42 kunnen zich per database anders gedragen: geen overeenkomst, een typefout of een automatische conversie. SQLite kan een TEXT-kolom met “0042” koppelen aan een INTEGER-kolom met 42. Gebruik consistente sleuteltypen en -notaties of een expliciete conversie die past bij de bedoelde betekenis.

Doorloop de eisen van het rapport

Vraag je af welke tabel alle rijen moet behouden. Volg het volledige sleutelpad en controleer elke voorwaarde aan de hand van het schema. Bepaal waarom elke koppeling een inner of outer join is en controleer daarna of elk filter bij het koppelen of daarna hoort. Vergelijk na elke stap de aantallen rijen en bekijk een bekende overeenkomende rij, een rij zonder overeenkomst en een rij met meerdere tegenhangers. Controleer ten slotte of elke telling en som het bedoelde soort record meet.