Opzoek naar een manier om uw rapportages op een snellere en eenvoudigere manier samen te kunnen stellen? >>

Uitleg SQL

1. Het SELECT statement (*)

Met het SELECT statement kan je gegevens uit één of meerdere tabellen opvragen.

Hierbij moet je een onderscheid maken tussen het SELECT statement zelf, dat eigenlijk een programma is, en de tabel die als resultaat van het SELECT statement wordt getoond. Deze tabel is virtueel. Dit betekent dat hij niet echt bestaat, maar bij het uitvoeren van het SELECT statement wordt deze opgesteld en bestaat slechts zolang het bijbehorende venster geopend blijft. Wanneer je gegevens in een virtuele tabel tracht te wijzigen, zullen de wijzigingen in werkelijkheid in de onderliggende echte tabellen (tables, ook base tables of reële tabellen genoemd) gewijzigd worden. In veel gevallen is er geen eenduidig verband tussen een rij of kolom uit een virtuele tabel en een rij of kolom uit een reële tabel. In die gevallen zal de virtuele tabel of een deel ervan niet kunnen worden gewijzigd.

In Access wordt een virtuele tabel die het resultaat is van een query een recordset genoemd. Heel vaak kan je hierin de inhoud van enkele of meerdere rijen of kolommen wijzigen. Dan spreekt Access van een dynaset (dynamische verzameling). Soms kan je geen enkele waarde in de resulterende tabel wijzigen. Dan spreekt Access van een snapshot (momentopname).

Hierbij zijn enkele belangrijke opmerkingen nodig:

Algemeen bevat een SELECT query zes soorten clausules, waarvan de eerste twee steeds voorkomen, en de vier andere kunnen voorkomen:

SELECT fieldlist
FROM tablelist
WHERE condition
GROUP BY group_fieldlist
HAVING group_condition
ORDER BY fieldlist;

1.1 SELECT in één tabel (*)

De eenvoudigste vorm van het SELECT statement haalt de gegevens uit één enkele tabel, die in de FROM clause wordt vermeld.

1.1.1 De SELECT en FROM clauses (*)

De SELECT en FROM clauses zijn steeds de eerste clauses van een SELECT statement.

De syntaxis van de SELECT en FROM clauses is:

SELECT []
    fieldname [AS alias] [, ...]
    FROM tablename

In zijn eenvoudigste vorm SELECT fieldname [, ...] FROM tablename; worden de aangegeven kolommen uit een tabel getoond in de opgegeven volgorde.

Als fieldname mag * worden vermeld. Dit is een korte notatie voor alle velden uit de tabel, in dezelfde volgorde als in de tabel.

Fieldname

hoeft niet noodzakelijk een veld uit een tabel te zijn. Het kan ook een berekend veld zijn zoals prijs*hoeveelheid. In dit laatste geval spreek je beter van een berekende kolom, aangezien deze kolom niet met een veld overeenkomt. De inhoud van een berekende kolom is read-only.

Je kan elke kolom een andere naam geven door na fieldname AS alias te vermelden. De alias geldt dan als alternatieve kolomnaam. Hij wordt als kolomhoofd getoond. Bij dubbele voorkomende kolommen of kolomnamen moet je een alias opgeven. Bij berekende kolommen hoef je geen alias op te geven, maar je doet dit wel best.

Als fieldname kan een zogenaamde aggregate function worden gebruikt, waarbij totalen voor alle records worden berekend, indien er geen GROUP BY clause is, en totalen per groep, indien er een GROUP BY clause is (zie infra). In standaard SQL zijn er vijf aggregate functions (COUNT - aantal, SUM - totaal, AVG - gemiddelde, MAX - grootste waarde, MIN - kleinste waarde). Access SQL heeft daarenboven extra aggregate functions (STDEV - standaardafwijking van de steekproef, VAR - variantie van de steekproef, STDEVP - standaardafwijking van de populatie, VARP - variantie van de populatie). Daarnaast kan je ook berekende totalen opstellen. Per groep wordt één rij getoond. Het resultaat is steeds een snapshot en kan niet worden gewijzigd.

Wanneer een aggregate function wordt gebruikt, moeten alle andere te tonen kolommen

Na het sleutelwoord SELECT kan één van vier sleutelwoorden volgen. Deze hebben de betekenis:

Voorbeelden:

Neem aan dat de tabel Klanten de volgende records en velden bevat:

klantnr

(primaire sleutel)

naam

gemeente

1

Janssens

Brussel

2

Janssens

Brussel

3

Peeters

Brussel

De hiernavolgende SELECT statements leveren de erbij vermelde recordsets op:

naam

gemeente

Janssens

Brussel

Janssens

Brussel

Peeters

Brussel

naam

gemeente

Janssens

Brussel

Peeters

Brussel

naam

gemeente

Janssens

Brussel

Janssens

Brussel

Peeters

Brussel

naam

gemeente

Janssens

Brussel

Janssens

Brussel

naam

gemeente

Janssens

Brussel

Janssens

Brussel

In dit laatste geval wordt het getoonde percentage (50% van 3) naar boven afgerond. Bij gelijke waarden voor het ORDER BY veld worden alle gelijk geordende records getoond.

Wanneer de eerste kolom in de tabel ontbreekt, en er dus geen primaire sleutel is, leveren de hiernavolgende SELECT statements de volgende recordsets op:

SELECT ALL naam, gemeente FROM Klanten; (of SELECT naam, gemeente FROM klanten;):

naam

gemeente

Janssens

Brussel

Janssens

Brussel

Peeters

Brussel

SELECT DISTINCT naam, gemeente FROM Klanten; :

naam

gemeente

Janssens

Brussel

Peeters

Brussel

SELECT DISTINCTROW naam, gemeente FROM Klanten; :

naam

gemeente

Janssens

Brussel

Peeters

Brussel

In dit laatste geval worden er slechts twee rijen getoond, omdat alle kolommen in de fieldlist voorkomen, en er twee identieke rijen zijn.

1.1.2 De WHERE clause (*)

De WHERE clause is niet verplicht in een SELECT statement. Ze wordt gebruikt om via voorwaarden een restrictie op te leggen aan de te tonen records of rijen.

De syntaxis van de WHERE clause is:

WHERE condition

 

Hierbij is condition een uitdrukking waarvan de waarde TRUE of FALSE is. Ze wordt opgebouwd met de logische operatoren AND, OR en NOT en de vergelijkingsoperatoren =, <, >, <=, >= en <>. Daarenboven kunnen ook wildcards (*, ?, #, [ ] en !) en andere operatoren zoals Like, In en Between voorkomen. Het gebruik van ( ) dient om de volgorde van de evaluatie van de operatoren te bepalen.

De Like operator vergelijkt een tekstuitdrukking in het linkerlid, die een wildcard bevat, met een patroon tussen " "in het rechterlid. Een patroon is een veralgemeende tekstuitdrukking, d.w.z. een tekst waarin nog enige vrijheid bestaat. Zo staat "A*" voor alle teksten die met een A beginnen, en staat "?end" voor alle teksten van vier tekens waarbij het eerste teken volledig willekeurig is en de laatste drie letterlijk "end" zijn.

De betekenis van de wildcards is als volgt:

* Nul, één of meer willekeurige tekens

? Exact één willekeurig teken

# Exact één willekeurig cijfer

[ ] Een reeks tekens tussen [ ] betekent exact één teken uit de opgegeven reeks. Binnen de [ ] betekent een streepje (-) een bereik van tekens. [A-Za-z] staat dus voor een willekeurige letter. Een uitroepteken betekent een uitsluiting. [!0-9] staat dus voor een willekeurig teken dat geen cijfer is. Wanneer de wildcards als teken worden bedoeld, staan ze tussen [ ].

Voorbeelden (tracht zelf de betekenis uit te zoeken):

SELECT * FROM Studenten
WHERE woonplaats = "Antwerpen";

SELECT * FROM Studenten
WHERE achternaam >="N";

SELECT * FROM Studenten
WHERE achternaam Like [!V]*

SELECT * FROM Studenten
WHERE NOT achternaam Like "V*"

SELECT * FROM Studenten
WHERE postcode Like "[1-3]###"

SELECT * FROM Begeleiders
WHERE vak Like "*[?]*"

SELECT * FROM Studenten
WHERE woonplaats In ("Brussel", "Gent", "Leuven");

SELECT * FROM Studenten
WHERE woonplaats = "Brussel" OR woonplaats = "Gent"
OR woonplaats = "Leuven";

SELECT * FROM Studenten
WHERE woonplaats = "Brussel" OR achternaam Like "V*";

SELECT * FROM Studenten
WHERE woonplaats = "Brussel" AND achternaam Like "V*";

SELECT * FROM Studenten
WHERE geb_datum Between #09/15/72# And #12/31/72#;

SELECT * FROM Studenten
WHERE geb_datum >= #09/15/72# AND geb_datum <= #12/31/72#;

SELECT stagenr, [einde stage] - [begin stage] AS [duur stage]
FROM Studenten
WHERE [begin stage] >="01/01/72#;

1.1.3 De ORDER BY clause (*)

De ORDER BY clause is niet verplicht in een SELECT statement. Ze wordt gebruikt om een ordening op te leggen aan de te tonen records of rijen.

De syntaxis van de ORDER BY clause is:

ORDER BY fieldname [][, ...]

De getoonde rijen worden geordend op de waarden in de kolommen die worden opgegeven in de ORDER BY clause. Indien er meerdere kolommen zijn, wordt eerst geordend op de eerste kolom, daarna op de volgende enz. Standaard wordt stijdend geordend (0-9 en A-Z). Je kan de volgorde van ordenen wijzigen door na het fieldname ASC (stijgend) of DESC (dalend) te vermelden. Als fieldname moeten veldnamen of uitdrukkingen worden gebruikt, maar geen aliassen!

Wanneer de ORDER BY clause ontbreekt, staat de volgorde van de rijen niet vast.

1.1.4 De GROUP BY clause (*)

De GROUP BY clause is niet verplicht in een SELECT statement. Ze wordt gebruikt wanneer via aggregate functions totalen berekend worden voor groepen records i.p.v. voor alle records samen.

De syntaxis van de GROUP BY clause is:

GROUP BY field [, ...]

Een groep bestaat uit alle records met gelijke waarden voor alle velden (of uitdrukkingen) in de GROUP BY clause. Niet in de GROUP BY clause vermelde kolommen die wel in de SELECT clause voorkomen, moeten een aggregate function bevatten.

Voorbeelden:

SELECT Count(*) AS aantal, Avg([einde stage] - [begin stage]) AS [gemiddelde duur] FROM Stages;

SELECT Count(*) AS aantal, Avg([einde stage] - [begin stage]) AS [gemiddelde duur] FROM Stages
GROUP BY instellingsnr;

In de eerste query wordt het aantal stages en de gemiddelde duur van een stage berekend. De laatste formule is een berekend totaal. Er is één enkele rij.

In de tweede query worden dezelfde berekeningen gemaakt, maar dan opgesplitst per instelling. Er is één rij per instelling.

1.1.5 De HAVING clause (*)

De HAVING clause is niet verplicht in een SELECT statement. Ze wordt gebruikt om via voorwaarden een restrictie op te leggen aan groepen. Dit betekent dat ze in principe enkel samen met de GROUP BY clause voorkomt .

De syntaxis van de HAVING clause is:

HAVING condition

Hierbij is condition een uitdrukking waarvan de waarde TRUE of FALSE is. Ze wordt opgebouwd met de logische operatoren AND, OR en NOT en de vergelijkingsoperatoren =, <, >, <=, >= en <>. Daarenboven kunnen wildcards zoals * en ? en andere operatoren zoals Like, In en Between voorkomen. Het gebruik van haakjes ( ) dient om de volgorde van de evaluatie van de operatoren te bepalen.

Het verschil tussen de WHERE en HAVING clauses bestaat er in dat de velden in de condition van de HAVING clause ofwel velden zijn die binnen een groep één waarde aannemen (maar dan kan een WHERE clause worden gebruikt, wat efficiënter is), ofwel aggregate functions bevatten.

Een tweede verschil tussen de WHERE en de HAVING clause betreft het ogenblik waarop de voorwaarde wordt getest. Bij de WHERE clause gebeurt dit voor individuele rijen vóór het groeperen; bij de HAVING clause gebeurt dit voor de groepstotalen na het groeperen.

Aan de hand van enkele voorbeelden wordt hierna het verschil in het gebruik van WHERE en HAVING geïllustreerd. De queries zijn gebaseerd op de volgende tabel Klanten:

naam (primaire sleutel)

geslacht

woonplaats

omzet

Adriaens

vrouw

Brugge

50 000

Bellens

man

Brussel

20 000

De Bock

vrouw

Gent

44 000

De Groot

vrouw

Brussel

36 000

Mortier

man

Kortrijk

15 000

Peeters

man

Brussel

29 000

Rogiers

man

Gent

97 000

Van den Broecke

man

Antwerpen

35 000

Willems

vrouw

Antwerpen

27 000

Wouters

man

Lier

16 000

 

SELECT woonplaats, Count(*) AS aantal, Sum(omzet) AS [totale omzet] FROM Klanten
WHERE geslacht = "man"
GROUP BY woonplaats
ORDER BY woonplaats;

woonplaats

aantal

totale omzet

Antwerpen

1

35 000

Brussel

2

49 000

Gent

1

97 000

Kortrijk

1

15 000

Lier

1

16 000

SELECT woonplaats, Count(*) AS aantal, Sum(omzet) AS [totale omzet] FROM Klanten
GROUP BY woonplaats
HAVING Count(*)>1
ORDER BY woonplaats;

woonplaats

aantal

totale omzet

Antwerpen

2

62 000

Brussel

3

85 000

Gent

2

141 000

SELECT woonplaats, Count(*) AS aantal, Sum(omzet) AS [totale omzet] FROM Klanten
WHERE woonplaats = "Brussel" OR woonplaats = "Antwerpen"
GROUP BY woonplaats
ORDER BY woonplaats;

SELECT woonplaats, Count(*) AS aantal, Sum(omzet) AS [totale omzet] FROM Klanten
GROUP BY woonplaats
HAVING woonplaats = "Brussel" OR woonplaats = "Antwerpen"
ORDER BY woonplaats;

woonplaats

aantal

totale omzet

Antwerpen

2

62 000

Brussel

3

85 000

SELECT woonplaats, Count(*) AS aantal, Sum(omzet) AS [totale omzet] FROM Klanten
WHERE geslacht = "man"
GROUP BY woonplaats
HAVING Count(*)>1
ORDER BY woonplaats;

woonplaats

aantal

totale omzet

Brussel

2

49 000

3.2 SELECT in meerdere tabellen (*)

Naast queries gebaseerd op één tabel zijn er ook queries gebaseerd op meerdere tabellen. Hierbij worden tabellen samengevoegd. Men spreekt dan van een join tussen tabellen. Er zijn drie grote soorten joins:

Je kan elk van deze joins aanduiden m.b.v. de FROM en de WHERE clause:

Algemene vereenvoudigde syntaxis (de niet relevante clauses worden weggelaten):

SELECT fieldlist
FROM table1, table2
WHERE table1.field1 = table2.field2;

  • Theta join
    • :

    SELECT fieldlist
    FROM table1, table2
    WHERE table1.field1 T table2.field2;

    SELECT fieldlist
    FROM table1, table2;

    Hierbij zijn table1 en table2 de gejoinde tabellen. Field1 en field2 zijn de velden uit resp. table1 en table2 die de relatie bepalen. T is een symbool dat een vergelijkingsoperator verschillend van = aanduidt, bijvoorbeeld kleiner dan (<) of groter dan (>) (Gewoonlijk wordt hier de Griekse hoofdletter Theta voor gebruikt, maar op het world wide web kiezen we de gewone letter T).

    Algemeen zullen veldnamen in een query gebaseerd op meer dan één tabel worden voorafgegaan door de naam van de tabel waaruit zij afkomstig zijn en een punt. Wanneer er geen dubbelzinnigheden zijn mag je deze tabelnaam (en het punt) echter weglaten.

    In Access SQL en SQL/92 kan bij een equi join de join voorwaarde in de WHERE clause worden vermeld, maar zal ze standaard in de FROM clause worden vermeld. Deze heeft dan de gedaante

    SELECT fieldlist
    FROM table1 [jointype] JOIN table2
    ON table1.field1 = table2.field2;

    Deze formulering is enkel mogelijk bij een equi join bij een one to many of een one to one relatie. De linkse tabel table1 is dan steeds de tabel aan de one zijde van de one to many relatie en de rechtse tabel table2 de tabel aan de many zijde van de one to many relatie.

    Het jointype kan in Access drie waarden aannemen (zie infra).

    Wanneer eenmaal tabellen zijn samengevoegd kunnen de WHERE, GROUP BY, HAVING en ORDER BY clauses worden gebruikt zoals bij één tabel queries. In de resulterende dynaset zullen wel sommige gegevens (uit tabellen aan de one zijde van de relatie) herhaald worden getoond, met allerlei gevolgen voor de mogelijkheid tot wijzigen (niet mogelijk of een wijziging in meerdere rijen tegelijk!). Hierop wordt in deze tekst echter niet verder ingegaan.

    1.2.1 INNER JOIN (*)

    Een inner join bevat enkel records uit beide tabellen waartussen een equi join relatie bestaat. Dit is de standaard join, die wordt aangeduid met de het jointype INNER. Records uit één van beide tabellen die geen corresponderend record hebben in de andere tabel worden niet getoond in de dynaset.

    SELECT fieldlist
    FROM table1 INNER JOIN table2
    ON table1.field1 = table2.field2;

    Voorbeeld (one to many relatie Studenten - Stagiaires):

    SELECT Stagiaires.studentnr, achternaam, voornaam, stagenr, begeleidersnr
    FROM Studenten INNER JOIN Stagiares ON Studenten.studentnr = Stagiaires.studentnr;

    Enkel de namen van de studenten die als stagiares stage lopen worden vermeld, met het nummer van hun stage en van hun begeleider.

    1.2.2 LEFT JOIN

    Een left outer join bevat alle records uit de tabel table1 aan de one zijde van de relatie en enkel die records uit de tabel table2 aan de many zijde van de relatie waarvoor een corresponderend record in de andere (one) tabel table1 bestaat. Deze join wordt aangeduid met het jointype LEFT. De join wordt een left outer join genoemd omdat de tabel aan de one zijde van de relatie steeds links van de sleutelwoorden LEFT JOIN staat.

    SELECT fieldlist
    FROM table1 LEFT JOIN table2
    ON table1.field1 = table2.field2;

    Voorbeeld (one to many relatie Studenten - Stagiaires):

    SELECT Studenten.studentnr, achternaam, voornaam, stagenr, begeleidersnr
    FROM Studenten INNER JOIN Stagiares ON Studenten.studentnr = Stagiaires.studentnr;

    Alle namen van de studenten worden vermeld, en voor de studenten die stage lopen ook het nummer van hun stage en van hun begeleider.

    1.2.3 RIGHT JOIN

    Een right outer join bevat alle records uit de tabel table2 aan de many zijde van de relatie en enkel die records uit de tabel table1 aan de one zijde van de relatie waarvoor een corresponderend record in de andere (many) tabel table2 bestaat. Deze join wordt aangeduid met het jointype RIGHT. De join wordt een right outer join genoemd omdat de tabel aan de many zijde van de relatie steeds rechts van de sleutelwoorden RIGHT JOIN staat.

    SELECT fieldlist
    FROM table1 RIGHT JOIN table2
    ON table1.field1 = table2.field2;

    Voorbeeld (one to many relatie Studenten - Stagiaires):

    SELECT Stagiaires.studentnr, achternaam, voornaam, stagenr, begeleidersnr
    FROM Studenten RIGHT JOIN Stagiares ON Studenten.studentnr = Stagiaires.studentnr;

    Alle nummers van stagiaires worden vermeld, met daarbij de nummers van de studenten, stages en begeleiders.

    Omdat met elke stagiare (per definitie) een student correspondeert, is het resultaat hetzelfde als bij de inner join. Zoiets gebeurt zeer vaak bij right outer joins.

    Right outer joins treden in de praktijk vooral op bij many to many relaties die opgesplitst zijn in een one to many en een many to one relatie.

    Voorbeeld (many to many relatie Studenten - Stagiaires - Begeleiders):

    SELECT Studenten.studentnr, Studenten.achternaam, Studenten.voornaam, stagenr, Stagiares.begeleidersnr, Begeleiders.voornaam
    FROM Begeleiders RIGHT JOIN (Studenten LEFT JOIN Stagiaires
    ON Studenten.studentnr = Stagiaires.studentnr)
    ON Begeleiders.begeleidersnr = Stagiaires.begeleidersnr;

    De plaatsing van de ( ) en de volgorde van de tabellen is steeds zó dat een one tabel steeds links van de gejoinde many tabel (of combinatie) staat:

    Het combineren van uitsluitend inner joins onderling is steeds toegelaten. Het combineren van uitsluitend outer joins is steeds toegelaten wanneer de tabel waarvan alle rijen getoond worden een outer join vertoont met alle andere tabellen (dit betekent in de grafische voorstelling van de joins in het Query Design View dat opeenvolgende pijlen nooit naar elkaar toe mogen wijzen). Het combineren van inner en outer joins is echter meestal niet toegelaten. Bij het bewaren of tonen van de dynaset treedt dan steeds een foutmelding op.

    Wanneer bij dezelfde many to many relatie alle begeleiders en enkel de studenten met een begeleider moeten worden getoond, neemt het SQL statement de volgende gedaante aan:

    SELECT Stagiaires.studentnr, Studenten.achternaam, Studenten.voornaam, Stagiaires.stagenr, Begeleiders.begeleidersnr, Begeleiders.voornaam
    FROM Studenten RIGHT JOIN (Begeleiders LEFT JOIN Stagiaires
    ON Begeleiders.begeleidersnr = Stagiaires.begeleidersnr)
    ON Studenten.studentnr = Stagiaires.studentnr;

     

    http://docent.ehsal.be/vakken/infoburo/Access/SQL.html#_Toc415901451