SQL Joins, subselects en overige manipulaties

Inleiding Joins Subselects Overige manipulaties

Inleiding

In dit hoofdstuk wordt voornamelijk het tegelijkertijd selecteren van gegevens uit meerdere tabellen behandeld, subselecties kunnen daar ook een rol in spelen.
Eenvoudiger maar zeker niet onbelangrijker is de SQL code voor het manipuleren van gegevens: invoeren, wijzigen en verwijderen.

Aan het einde van dit hoofdstuk :

Joins

Tot nu toe heb je alleen maar selecties uitgevoerd op één tabel. Heel vaak is het nodig om gegevens uit meerdere tabellen te combineren. Om een nota voor een order op te maken moeten gegevens van de klant, van de order en de in de order voorkomende producten in één op het scherm komen. Deze informatie kan met één SQL opdracht opgevraagd worden.
Het koppelen van meerder tabellen in één SQL opdracht wordt in SQL taal joinen genoemd. Een voorwaarde voor het koppelen, is dat er in de te koppelen tabellen velden aanwezig zijn die de relatie tussen tabellen weergeven. Voor de volledigheid geven we hieronder nogmaals schematisch weer hoe de tabellen gekoppeld zijn:

Hoewel er meerdere vormen van joinen bestaat wordt er hier slechts één behandeld, de INNER JOIN. We geven ook nog een computertijdrovender alternatief om het zelfde te bereiken. De structuur van een INNER JOIN selectie met twee tabellen wordt toegelicht aan de hand van het volgende voorbeeld:

SELECT * FROM klant INNER JOIN order ON klant.id = order.klantid
Achter FROM staat de koppeling tussen de tabel klant en order. De gegevens uit een regel in de klant tabel wordt gekoppeld aan een regel in de order tabel alleen als de waarde van het veld id in de tabel klant gelijk is aan de waarde van het veld klantid in de tabel order. SQL staat een koppeling op andere velden mag er natuurlijniet in de weg maar het ERD laat zien dat er geen andere koppeling kan zijn.
Een alternatief met het zelfde resultaat is:
SELECT  * FROM klant, order 
WHERE klant.id = order.klantid
Het grote nadeel van deze vorm is dat eerst iedere regel van de ene tabel wordt gekoppeld aan de andere waardoor er veel meer geheugenruimte in de server computer wordt gebruikt. Daarna worden de gevraagde regels er pas uitgefilterd.

De SQL code voor de nota moet een selectie zijn uit 4 tabellen, klant, oder, order_product en product. De code is de volgende:

SELECT * FROM ((klant INNER JOIN `order` ON klant.id = order.klantid) 
               INNER JOIN order_product  ON order_product.orderid = order.id)
               INNER JOIN product  ON order_product.productid = product.id
Je ziet het wordt al snel gecompliceerd als er veel tabellen moeten worden gekoppeld.

Opdrachten
  1. Het voorbeeld hierboven maakt een overzicht van klanten, orders en artikelen. Bekijk het resultaat van de query. Wat heb je nodig voor een nota?
  2. Maak een overzicht van de klanten en de orders.
  3. Geef het totale orderbedrag weer per order. (HINT: GROUP BY)
  4. Geef naast het totale orderbedrag uit de vorige opdracht ook de klantgegevens weer.
  5. Geef van de order bij opdracht 28 de omzet inclusief btw.

Subselects

Je hebt nu selecties gehad op één of meerdere tabellen. Bij subselects kun je meerdere selecties uitvoeren. Een query bestaat dan uit een hoofdquery en één of meerdere subqueries.

De hoofdquery bestaat doorgaans uit een algemene opdracht en de subselect uit details (wat er dan moet gebeuren). Een subselect moet je zien als een extra mogelijkheid om complexe sql-opdrachten ook te kunnen schrijven. Het is niet per definitie zo dat subselects het eindstation zijn als andere mogelijkheden niet gebruikt kunnen worden. Je zult zeker in het eerste voorbeeld zien dat deze ook zonder subselect kan worden opgelost.

De volgende query bestaat uit een algemene hoofdquery waarin eerst alle artikelen worden geselecteerd. Daarna wordt de maximale prijs van dat artikel eruit gehaald. Deze query kan je natuurlijk ook zonder subquery oplossen!

SELECT *
FROM artikelen
WHERE prijs = (SELECT MAX(prijs) FROM artikelen)

In de volgende situatie moeten de producten worden weergegeven waarvoor nog geen bestelling is geschreven. Deze opdracht is lastiger. Je zou deze kunnen oplossen door gebruik te maken van een INNER JOIN. Een subquery is misschien wel eenvoudiger. Laten we eens kijken naar het volgende voorbeeld.
Je wilt als output het artikelnummer en artikelomschrijving weten. Dat betekent dat we de tabel artikel nodig hebben. Maar de bestellingen staan in de koppeltabel orders_artikelen.

SELECT artikelnummer, artikelomschrijving
FROM  artikelen
WHERE artikelnummer NOT IN (SELECT artikelnummer FROM orders_artikelen)
Opdrachten
  1. Maak een selectie die controleert of er klanten in het systeem staan die nog geen ordernummer hebben. Als dit niet zo is geeft je resultaat niks weer ofwel (0 rijen).Onze versie vind je
  2. Selecteer of er klanten zijn die meer dan twee aantallen per artikel hebben besteld. Onze versie vind je

Overige manipulaties

Overzichten maken met SQL op één of meerdere tabellen is nu hopelijk geen probleem meer voor jou. Maar alleen overzichten weergeven is niet zinvol. De gegevens moeten ook ingevoerd, bijgewerkt en verwijderd kunnen worden en dat moet in je eigen applicatie ook via SQL. Hieronder worden drie voorbeelden weggegeven voor: invoeren, bijwerken en verwijderen. Daarna volgen enkele opdrachten.

Om de soorten onderdelen bij het manipuleren van databases beter te onthouden, is er een afkorting (ezelsbruggetje) bedacht met de naam CRUD. CRUD staat voor (C)Create, R(Read), U(Update), D(Delete). Create of het maken hebben we alleen handmatig toegepast in Mysql. Read of uitlezen heb je toegepast door de select-opdrachten te maken. C)Create, U(Update) en D(Delete)zullen in deze paragraaf behandeld worden.

Het eerste voorbeeld voert een product met naam accu, een prijs van €100 met een voorraad van 8 en omschrijving "Kwaliteits accu van het merk Flash". in de tabel product. Wees er wel van bewust dat de namen die tussen haakjes () staan niet leeggelaten mogen worden. Hoewel het id een primaire sleutel is en dus een waarde moet krijgen in de database, heeft id geen waarde nodig in de query, omdat dit veld de eigenschap AI (auto increment) heeft.

INSERT INTO product ( `naam`, `prijs` , `voorraad`, `omschrijving`) VALUES ( 'accu', '100', '8', 'Kwaliteits accu van het merk Flash')

Het tweede voorbeeld wijzigt alleen de prijs van het product met id 12. Wil je meerdere onderdelen veranderen dan zet je na prijs = '76' nog een veldnaam neer voorafgaand aan een komma.

UPDATE product SET prijs = '76' WHERE id = 12
Let op: zonder "WHERE" conditie wordt de prijs van ieder product 76!

Het derde voorbeeld verwijdert het artikel met nummer 12.

DELETE FROM product WHERE id = 12
Let op: zonder "WHERE" conditie worden alle producten verwijderd!

Opdrachten
  1. Maak een overzicht van alle klanten uit tabel klant.
  2. Het unieke klantnummer binnen de tabel klant is de waarde van het veld id. Geef de code voor het vinden van het hoogste klantnummer in deze tabel.
  3. Voer nu in de tabel klant je eigen gegevens in.
  4. Wijzig nu bijvoorbeeld de woonplaats of klantadres. Gebruik het klantnummer uit opdracht 9.
  5. Verwijder nu je eigen ingevoerde klantgegevens. Gebruik het klantnummer uit opdracht 10.