6. Verdieping in de taal SQL
6.1. Introduction
In dit hoofdstuk presenteren we
- andere syntaxisvormen van het commando SELECT, waardoor dit een zeer krachtig opvraagcommando is, met name om meerdere tabellen tegelijk te raadplegen.
- uitgebreide syntaxis van reeds besproken commando’s
Om de verschillende commando's te illustreren, werken we met de volgende tabellen die worden gebruikt voor het beheer van bestellingen in een PME-systeem voor de distributie van boeken:
6.1.1. de tabel CLIENTS
Deze tabel bevat informatie over de klanten van de PME:
 |
uniek identificatienummer van de klant – primaire sleutel | |
naam van de klant | |
I = Particulier, E = Bedrijf, A = Overheid | |
voornaam in het geval van een particulier | |
Naam van de contactpersoon bij de klant (in het geval van een bedrijf of overheidsinstantie) | |
Adres van de klant - straat | |
plaats | |
postcode | |
Telefoonnummer | |
Sinds wanneer klant? | |
O (Ja) als de klant het bedrijf geld verschuldigd is en N (Nee) anders. |
6.1.2. de tabel ARTICLES
Deze tabel bevat informatie over de verkochte producten, in dit geval boeken. De structuur is als volgt:
een uniek identificatienummer voor een boek (ISBN = International Standard Book Number) – primaire sleutel | |
Titel van het boek | |
Code die een uitgever uniek identificeert | |
Naam van de auteur | |
Samenvatting van het boek | |
Verkochte hoeveelheid in het jaar | |
Verkochte hoeveelheid vorig jaar | |
Datum van de laatste verkoop | |
Hoeveelheid van de laatste levering | |
Datum van de laatste levering | |
Verkoopprijs | |
Aankoopprijs | |
Minimale bestelhoeveelheid | |
Minimale voorraad | |
Voorraad |
De inhoud zou als volgt kunnen zijn:
6.1.3. de tabel COMMANDES
Hierin wordt informatie over de bestellingen van klanten opgeslagen. De structuur is als volgt:
Uniek identificatienummer van een bestelling – primaire sleutel | |
Klantnummer van degene die deze bestelling plaatst - buitenlandse sleutel - referentie CLIENTS(ID) | |
Datum waarop deze bestelling is ingevoerd | |
O (Ja) als de bestelling is geannuleerd en N (Nee) anders. |
6.1.4. de tabel DETAILS
Deze bevat de details van een bestelling, d.w.z. de referenties en aantallen van de bestelde boeken. De structuur is als volgt:
Bestelnummer – externe sleutel die verwijst naar kolom NOCMD van de tabel COMMANDES | |
Nummer van het bestelde boek - externe sleutel die verwijst naar kolom ISBN van de tabel LIVRES | |
Bestelde hoeveelheid |
De inhoud zou als volgt kunnen zijn:
Hierboven zien we dat bestelling nr. 3 (NOCMD) betrekking heeft op drie boeken. Dit betekent dat de klant drie boeken tegelijk heeft besteld. De referenties van deze klant zijn te vinden in de tabel [COMMANDES], waaruit blijkt dat bestelling nr. 3 is geplaatst door klant nr. 5. Uit de tabel [CLIENTS] blijkt dat klant nr. 5 het bedrijf NetLogos uit Segré is.
6.2. De bestelling SELECT
We willen hier onze kennis van de bestelling SELECT verdiepen door nieuwe syntaxisvormen ervan te presenteren.
6.2.1. Syntaxis van een query met meerdere tabellen
SELECT kolom1, kolom2, ... FROM tabel1, tabel2, ..., tabelp WHERE condition ORDER BY ... | |
Het nieuwe hier is dat de kolommen kolom1, kolom2, ... afkomstig zijn uit verschillende tabellen tabel1, tabel2, ... Als twee tabellen kolommen met dezelfde naam hebben, wordt de dubbelzinnigheid opgeheven door de notatie tablei.colonnej. De notatie condition kan betrekking hebben op kolommen uit verschillende tabellen. |
Werking
De cartesiaanse producttabel van table1, table2, ..., tablep wordt samengesteld. Als ni het aantal rijen van tablei is, dan heeft de samengestelde tabel dus n1*n2*...*np rijen die alle kolommen van de verschillende tabellen bevatten. | |
De condition van de WHERE wordt op deze tabel toegepast. Zo ontstaat een nieuwe tabel | |
Deze is gesorteerd volgens de wijze die is aangegeven in ORDER. | |
De kolommen die in SELECT zijn opgevraagd, worden weergegeven. |
Voorbeelden
We gebruiken de eerder gepresenteerde tabellen. We willen de details zien van de bestellingen die na 25 september zijn geplaatst:
SQL>select details.nocmd,isbn,qte from commandes,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
Merk op dat achter FROM de naam wordt vermeld van alle tabellen waarvan de kolommen worden geraadpleegd. In het vorige voorbeeld behoren de geselecteerde kolommen allemaal tot de tabel DETAILS. De voorwaarde verwijst echter naar de tabel COMMANDES. Daarom moet deze laatste achter FROM worden vermeld. De bewerking waarbij wordt getoetst of kolommen uit twee verschillende tabellen gelijk zijn, wordt vaak een equijunctie genoemd.
De query SELECT had ook als volgt geschreven kunnen worden:
SQL> select details.nocmd,isbn,qte from commandes
inner join details on details.nocmd=commandes.nocmd
where commandes.datecmd>'25-sep-91'
Laten we verdergaan met onze voorbeelden. We willen hetzelfde resultaat als eerder, maar dan met de titel van het bestelde boek in plaats van het nummer ISBN:
SQL>select commandes.nocmd, articles.titre, details.qte
from commandes,articles,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
Hetzelfde resultaat wordt verkregen met de volgende, minder leesbare query SQL:
SQL> select details.nocmd,articles.titre,details.qte from details
inner join commandes on details.nocmd=commandes.nocmd
inner join articles on details.isbn=articles.isbn
where commandes.datecmd>'25-sep-91'
Hierboven worden twee interne koppelingen gemaakt met de tabel [DETAILS]:
- de ene met de tabel [COMMANDES] om toegang te krijgen tot de besteldatum van een boek
- de ene met de tabel [ARTICLES] om toegang te krijgen tot de titel van het bestelde boek
Bovendien willen we de naam van de klant die de bestelling plaatst:
SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
Daarnaast willen we de besteldata en een weergave van deze data in aflopende volgorde:
SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
order by commandes.datecmd descending
Hier volgen enkele regels die bij het samenvoegen in acht moeten worden genomen:
- Achter SELECT zet je de kolommen die je in de weergave wilt zien. Als de kolom in verschillende tabellen voorkomt, zet je de naam van de tabel ervoor.
- Achter FROM zet je alle tabellen die door de SELECT worden doorzocht, dat wil zeggen de tabellen waarin de kolommen staan die achter SELECT en WHERE staan.
6.2.2. De auto-join
We willen weten welke boeken een verkoopprijs hebben die hoger is dan die van het boek 'Using SQL':
SQL>select a.titre from articles a, articles b
where b.titre='Using SQL'
and a.prixvente>b.prixvente
De twee tabellen in de join zijn hier identiek: de tabel articles. Om ze van elkaar te onderscheiden, geven we ze een alias: from articles a, articles b. De alias van de eerste tabel heet a en die van de tweede b. Deze syntaxis kan ook worden gebruikt als de tabellen verschillend zijn. Wanneer een alias wordt gebruikt, moet deze overal in de opdracht SELECT worden gebruikt in plaats van de tabel waarnaar deze verwijst.
6.2.3. Externe join
We willen weten welke klanten in september iets hebben gekocht, met vermelding van de besteldatum. De overige klanten worden zonder deze datum weergegeven:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where datecmd between '01-sep-91' and '30-sep-91'
Het verbaast ons dat we hier niet het juiste resultaat krijgen. Alle klanten zouden in de tabel [CLIENTS] moeten staan, wat niet het geval is. Als we nadenken over hoe de externe join werkt, beseffen we dat de klanten die niets hebben gekocht, zijn gekoppeld aan een lege rij in de tabel COMMANDES en dus aan een lege datum (waarde NULL in de terminologie SQL). Deze datum voldoet dus niet aan de voorwaarde die is ingesteld voor de datum en de betreffende klant wordt niet weergegeven. Laten we iets anders proberen:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where (commandes.datecmd between '01-sep-91' and '30-sep-91')
or (commandes.datecmd is null)
Dit keer krijgen we het juiste antwoord op onze vraag.
6.2.4. Geneste zoekopdrachten
SELECT kolom[s] FROM tabel[s] WHERE uitdrukking operator query ORDER BY ... | |
requête is een commando SELECT dat een groep van 0, 1 of meerdere waarden oplevert. We hebben dan een voorwaarde WHERE van het type uitdrukking operator (val1, val2, ..., vali) expression en vali moeten van hetzelfde type zijn. Als de query slechts één waarde oplevert, komt men uit bij een voorwaarde van het type uitdrukking operator waarde die we goed kennen. Als de query een lijst met waarden oplevert, kunnen we de volgende operatoren gebruiken:
expression IN (val1, val2, ..., vali): waar als expression de waarde heeft van een van de elementen uit de lijst vali.
omgekeerd van IN
moet worden voorafgegaan door =, !=, >, >=, <, <= expression >= ANY (val1, val2, .., valn): waar als expression >= is aan een van de waarden vali uit de lijst
moet worden voorafgegaan door =, !=, >, >=, <, <= expression >= ALL (val1, val2, .., valn): waar als de uitdrukking >= is aan alle waarden vali uit de lijst
query: waar als de requête ten minste één rij retourneert. |
Voorbeelden
We nemen de vraag die al met een equi-join is opgelost: toon de titels met een verkoopprijs die hoger is dan die van het boek 'Using SQL'.
SQL>select titre from ARTICLES
where prixvente > (select prixvente from ARTICLES where titre='Using SQL')
Deze oplossing lijkt intuïtiever dan die met de equijunctie. We voeren eerst een filter uit op SELECT en vervolgens een tweede filter op het verkregen resultaat. Op deze manier kunnen we meerdere filters achter elkaar toepassen.
We willen weten welke titels een verkoopprijs hebben die hoger is dan de gemiddelde verkoopprijs:
Welke klanten hebben de titels besteld die het resultaat zijn van de vorige zoekopdracht?
SQL>select distinct idcli from COMMANDES,DETAILS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
Toelichting
- in de tabel DETAILS worden de codes ISBN geselecteerd die voorkomen bij boeken waarvan de prijs hoger is dan de gemiddelde prijs van de boeken.
- In de geselecteerde rijen uit de vorige stap ontbreekt de klantcode IDCLI. Deze bevindt zich in de tabel COMMANDES. De koppeling tussen de twee tabellen gebeurt via het bestelnummer NOCMD, vandaar de equijunctie COMMANDES.nocmd=DETAILS.nocmd.
- Eenzelfde klant kan een van de betreffende boeken meerdere keren hebben gekocht, in welk geval zijn code IDCLI meerdere keren voorkomt. Om dit te voorkomen, plaatsen we het sleutelwoord DISTINCT achter SELECT. DISTINCT verwijdert in het algemeen duplicaten uit de resultaatregels van een SELECT.
- Om de naam van de klant te verkrijgen, zouden we een extra equijunctie moeten uitvoeren tussen de tabellen COMMANDES en CLIENTS, zoals te zien is in de volgende query.
SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
and COMMANDES.IDCLI=CLIENTS.ID
Zoek klanten die sinds 24 september geen bestelling hebben geplaatst:
SQL>select nom from CLIENTS
where clients.id not in
(select distinct commandes.idcli from commandes where datecmd>='24-sep-91')
We hebben gezien dat we rijen ook op een andere manier kunnen filteren dan met de clausule WHERE: door de clausule HAVING te gebruiken in combinatie met de clausules GROUP en BY. De clausule HAVING filtert groepen regels.
Net als bij de clausule WHERE is de syntaxis
HAVING expression opérateur requête
is mogelijk, met de reeds genoemde beperking dat expression een van de uitdrukkingen expri in de clausule
GROUP BY expr1, expr2, ...
Voorbeelden
Wat zijn de verkochte aantallen voor boeken met meer dan 200F?
Laten we eerst de verkochte aantallen per titel weergeven:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
Laten we nu de titels filteren:
SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
having titre in (select titre from ARTICLES where prixvente>200)
Misschien zou het duidelijker zijn geweest om het als volgt te schrijven:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
and ARTICLES.prixvente>200
group by titre
6.2.5. Gekoppelde query's
Bij geneste query's is er sprake van een bovenliggende query (de meest buitenste query) en een onderliggende query (de meest binnenste query). De bovenliggende query wordt pas geëvalueerd nadat de onderliggende query volledig is geëvalueerd.
Gekoppelde query’s hebben dezelfde syntaxis, op het volgende detail na: de dochterquery voert een join uit op de tabel van de moederquery. In dit geval wordt de combinatie van moeder- en dochterquery herhaaldelijk geëvalueerd voor elke rij van de moeder-tabel.
Voorbeeld
We nemen het voorbeeld waarin we de namen willen hebben van klanten die sinds 24 september geen bestelling hebben geplaatst:
SQL>
select nom from clients
where not exists
(select idcli from commandes
where datecmd>='24-sep-91'
and commandes.idcli=clients.id)
De bovenliggende query wordt uitgevoerd op de tabel clients. De onderliggende query voert een join uit tussen de tabellen clients en commandes. We hebben dus te maken met een gecorreleerde query. Voor elke rij in de tabel clients wordt de dochterquery uitgevoerd: deze zoekt de klantcode id in de bestellingen die na 24 september zijn geplaatst. Als deze niet wordt gevonden (not exists), wordt de naam van de klant weergegeven. Vervolgens gaan we naar de volgende regel van de tabel clients.
6.2.6. Selectiecriteria voor het schrijven van de SELECT
We hebben al meerdere keren gezien dat het mogelijk is om hetzelfde resultaat te verkrijgen door de tabel SELECT op verschillende manieren te doorlopen. Laten we een voorbeeld nemen: klanten weergeven die iets hebben besteld:
Join
Geneste query's
levert hetzelfde resultaat op.
Gecorreleerde zoekopdrachten
SQL>
select nom from clients
where exists (select * from commandes where commandes.idcli=clients.id)
levert hetzelfde resultaat op.
De auteurs Christian MAREE en Guy LEDANT stellen in hun boek 'SQL, Inleiding, Programmeren en Beheersing' enkele selectiecriteria voor:
Prestaties
De gebruiker weet niet hoe het SGBD erin slaagt de gevraagde resultaten te vinden. Alleen door ervaring zal hij dus ontdekken dat de ene schrijfwijze beter presteert dan de andere. MAREE en LEDANT stellen op basis van ervaring dat gecorreleerde query’s over het algemeen trager lijken te zijn dan geneste query’s of joins.
Formulering
De formulering met geneste query’s is vaak leesbaarder en intuïtiever dan een join. Ze is echter niet altijd bruikbaar. Er zijn met name twee punten waar je op moet letten:
- De tabellen die de argumentkolommen van de SELECT (SELECT col1, col2, ...) bevatten, moeten na het sleutelwoord FROM worden genoemd. Vervolgens wordt het cartesiaanse product van deze tabellen uitgevoerd, wat een join wordt genoemd.
- Wanneer de query resultaten uit één enkele tabel weergeeft en het filteren van de rijen uit die tabel het raadplegen van een andere tabel vereist, kunnen geneste queries worden gebruikt.
6.3. Syntaxisuitbreidingen
Omwille van het gebruiksgemak hebben we meestal verkorte syntaxis van de verschillende commando’s gepresenteerd. In deze paragraaf presenteren we de uitgebreide syntaxis. Deze spreken voor zich, omdat ze analoog zijn aan die van het uitgebreid besproken commando SELECT.
INSERT
INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...) | |
INSERT INTO table (kolom1, kolom2, ..) (requête) | |
Deze twee syntaxisvormen zijn gepresenteerd |
DELETE
DELETE FROM table WHERE condition | |
Deze syntaxis is bekend. Hieraan kan worden toegevoegd dat de voorwaarde een query kan bevatten met de syntaxis WHERE uitdrukking operator (query) |
UPDATE
UPDATE table SET kol1=uitdruk1, kol2=uitdruk2, ... WHERE condition | |
Deze syntaxis is al eerder besproken. Hieraan kunnen we toevoegen dat de voorwaarde een query kan bevatten met de syntaxis WHERE uitdrukking operator (query) |
UPDATE table SET (kol1, kol2, ..) = query1, (kolA, kolB, ..) = query2, ... WHERE condition | |
De waarden die aan de verschillende kolommen worden toegewezen, kunnen afkomstig zijn uit een query. |