SQL Joins, subselects en overige manipulaties

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 :

• Kun je sqlopdrachten uitvoeren op meerdere tabellen;
• Kun je eenvoudige subselects uitvoeren;
• Kun je gegevens invoeren, wijzigen en verwijderen d.m.v. sql-opdrachten;

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

SELECT  * FROM klant, order 
WHERE klant.id = order.klantid

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

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.

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)

6. 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 hier

   SELECT voornaam, achternaam 
   FROM klant
   WHERE id NOT IN (SELECT klantid FROM order)
   

7. Selecteer of er klanten zijn die meer dan twee aantallen per artikel hebben besteld. Onze versie vind je hier

   SELECT voornaam, achternaam 
   FROM klant
   WHERE id IN (SELECT order.klantid FROM `order` INNER JOIN order_product WHERE order_product.aantal > 2)
   
   of
   
   SELECT DISTINCT klant.voornaam, klant.achternaam FROM (klant INNER JOIN `order` ON klant.id = order.klantid) 
                  INNER JOIN order_product ON order_product.orderid = order.id
   WHERE order_product.aantal > 2
   

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

Het derde voorbeeld verwijdert het artikel met nummer 12.

DELETE FROM product WHERE id = 12

8. Maak een overzicht van alle klanten uit tabel klant.
9. 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.
10. Voer nu in de tabel klant je eigen gegevens in.
11. Wijzig nu bijvoorbeeld de woonplaats of klantadres. Gebruik het klantnummer uit opdracht 9.
12. Verwijder nu je eigen ingevoerde klantgegevens. Gebruik het klantnummer uit opdracht 10.