Code is there to explain the comments to the computer.
—Andy Harris

De WHERE clause in detail

In een SELECT statement zoals:

SELECT opocode, coordinator
FROM opleidingsonderdeel
WHERE coordinator = 'u0012047'

Daarna wordt de WHERE clause bekeken (indien aanwezig). Alleen de rijen die voldoen aan de voorwaarde in de WHERE blijven bewaard. Alle andere rijen verdwijnen uit het geheugen. Pas daarna komt de SELECT. In dit stukje bekijken we meer specifiek een aantal mogelijkheden van de WHERE.

Standaard vergelijkingsoperatoren

SQL voorziet de standaard vergelijkingsoperatoren: = (gelijk aan), <> of != (niet gelijk aan), > (groter dan), < (kleiner dan), >= (groter dan of gelijk aan) en <= (kleiner dan of gelijk aan). Je kan getallen, strings en datum of tijd vergelijken. Volgende query toont alle OPO’s die een startdatum voor 5 april 2017 hebben:

SELECT *
FROM opleidingsonderdeel
WHERE van < '2017-04-05' -- datum in ISO-formaat met enkele aanhalingstekens invoeren

Een datum die meer in het verleden ligt is kleiner dan een latere datum, zoals je logisch mag verwachten.

Bij het vergelijken van strings komt er iets meer kijken. Jammer genoeg is het resultaat databasespecifiek. Dat 'a' < 'b' is evident, maar hoe zit het met de vergelijking van 'a' en 'A'? Weet je wat, we proberen het gewoon uit in PostgreSQL:

SELECT 'a' < 'A'

Het resultaat is true. We besluiten: in PostgreSQL komen kleine letters voor de hoofdletters. In een ander DBMS is het mogelijk net omgekeerd.

En wat met een vergelijking tussen 'aap' en 'appel' en tussen 'appel' en 'appelmoes'? Even testen:

SELECT 'aap' > 'appel';
SELECT 'appel' > 'appelmoes'

Het resultaat is twee keer false. De vergelijking tussen twee strings gebeurt namelijk letter per letter van links naar rechts. Bij 'aap' en 'appel' zijn de eerste letters gelijk, dus wordt de tweede letter bekeken. De tweede ‘a’ van 'aap' is kleiner dan de ‘p’ van 'appel' en dus is 'aap' < 'appel'.

Het woord 'appel' is kleiner dan 'appelmoes'. De letters worden één voor één bekeken. Na vijf overeenkomende letters is er geen zesde letter meer in de string 'appel' en dus is dit woord kleiner (korter) dan het langere woord 'appelmoes'.

IS (NOT) NULL

De waarde NULL in een veld betekent ‘onbekend’. Je kan vanzelfsprekend ook testen op deze speciale waarde. Je moet er alleen aan denken dat je niet = gebruikt maar wel IS. Volgende query toont een lijst van alle OPO’s waarvan nog niet bekend is wanneer die gaan stoppen:

SELECT *
FROM opleidingsonderdeel
WHERE tot IS NULL -- dus niet = NULL

Omgekeerd geeft de volgende query de lijst van alle OPO’s die wel een einddatum hebben (en die dus niet meer gegeven worden of waarvan nu al geweten is tot wanneer deze OPO’s in het curriculum zullen staan):

SELECT *
FROM opleidingsonderdeel
WHERE tot IS NOT NULL

Stringpatronen met LIKE

Soms wil je niet zoeken op de volledige waarde van een string, maar slechts op een deel. Stel dat je een lijst wilt van alle OPO’s die het woord ‘web’ ergens in de naam hebben (zowel met grote of kleine letter). We weten al uit een vorig stuk dat we de functie lower() zeker nuttig zullen gebruiken in deze query om alle hoofdletters naar kleine letters om te zetten. Maar hoe testen we op ‘web’ ergens in de naam?

Dat kan op verschillend manieren (o.a. met reguliere expressies). We bekijken hier de eenvoudigste manier m.b.v. LIKE en een patroon. In dat patroon kan je naast gewone letters ook twee speciale tekens gebruiken:

• %: 0 of meerdere karakters;
• _ (underscore): juist 1 karakter.

De volgende query zoekt alle namen die ergens in de naam de substring ‘web’ bevatten (hoofdletters/kleine letters maken niet uit):

SELECT opocode, naam, semester
FROM opleidingsonderdeel
WHERE lower(naam) LIKE '%web%'

Maak nu de volgende oefeningen.

... LIKE 'B%'
... LIKE '_e%'
... LIKE '%e%e%e%'
... LIKE '__a_b%'

... LIKE 'B%' --alle strings die beginnen met hoofdletter B
... LIKE '_e%' --alle strings die als tweede letter een e hebben
... LIKE '%e%e%e%' --alle strings die minstens drie keer een e hebben
... LIKE '__a_b%' --alle strings met een a als derde en een b als vijfde letter

SELECT coordinator, naam
FROM opleidingsonderdeel
WHERE coordinator like '%4'
-- je had eventueel ook substring() kunnen gebruiken. Probeer je eens op die manier?

Rijen behouden die NIET aan een voorwaarde voldoen

Met een voorwaarde (conditie) in de WHERE clause houden we alleen die rijen over die voldoen aan die voorwaarde. Wat als je nu net alle andere rijen wilt hebben, dus die niet voldoen aan de voorwaarde? Hiervoor bestaat het keyword NOT. Volgende query geeft alle OPO’s die niet voldoen aan de voorwaarde, dus die niet beginnen met de letters ‘Web’:

SELECT opocode, naam, semester
FROM opleidingsonderdeel
WHERE NOT naam LIKE 'Web%'

SELECT opocode, naam, studiepunten
FROM opleidingsonderdeel
WHERE NOT naam like '%a%' -- aanpassing: NOT lower(naam) like '%a%'

Condities koppelen met AND en OR

We kunnen meerdere voorwaarden koppelen met AND en OR, trouwens ook in combinatie met NOT te gebruiken:

• A AND B: enkel TRUE als zowel A als B TRUE zijn;
• A OR B: TRUE als één van beide A of B of allebei TRUE zijn.

SELECT *
FROM opleidingsonderdeel
WHERE taal = 'nl' AND lower(naam) LIKE '%prog%'

SELECT *
FROM opleidingsonderdeel
WHERE taal = 'nl' OR taal = 'fr' -- we zien later een kortere manier om dit te noteren

Je kan meer dan twee voorwaarden koppelen. Je moet echter wel oppassen zoals volgend voorbeeld aantoont. Geef een lijst van alle OPO’s die in het Nederlands of het Frans gegeven worden en die enkel in het nieuwe curriculum zullen zitten. We proberen volgende query:

SELECT *
FROM opleidingsonderdeel
WHERE taal = 'nl' OR taal = 'fr' AND tot IS NULL

De volgorde van de operatoren is, van hoge prioriteit naar lage:

1. eerst vergelijkingsoperatoren zoals =, <, >, <=, …,
2. dan NOT,
3. vervolgens AND en
4. tenslotte als allerlaatste OR.

Dat betekent dat bovenstaande conditie niet gewoon van links naar rechts uitgevoerd wordt, maar als taal = 'nl' OR (taal = 'fr' AND tot IS NULL). Deze voorwaarde kan je vertalen als “alle Nederlandstalige OPO’s (van heden of verleden, maakt niet uit) ofwel alle Franstalige die in het huidige curriculum zitten”. Dat is niet wat we wilden.

De oplossing is: gebruik haakjes. De query zoals we hem bedoelden wordt dus:

SELECT *
FROM opleidingsonderdeel
WHERE (taal = 'nl' OR taal = 'fr') AND tot IS NULL

Bij complexe samengestelde voorwaarden zijn haakjes altijd een goed idee omdat ze de leesbaarheid van je conditie verhogen.

BETWEEN

We beginnen met een eenvoudige oefening:

SELECT opocode, naam, studiepunten
FROM opleidingsonderdeel
WHERE studiepunten >=3 AND studiepunten <=8

SQL biedt een wat kortere notatie voor dit soort query’s die beter aansluit bij het gewone taalgebruik:

SELECT opocode, naam, studiepunten
FROM opleidingsonderdeel
WHERE studiepunten BETWEEN 3 AND 8 -- beide grenzen zijn inbegrepen

Als je op een examen zelf query’s moet schrijven, mag je altijd kiezen of je voor de BETWEEN notatie of de iets langere versie gaat. Je moet beide notaties natuurlijk wel snappen.

IN

Met het keyword IN kan je een voorwaarde korter noteren. Ook hier weer een kleine oefening om te beginnen:

SELECT opocode, naam, coordinator
FROM opleidingsonderdeel
WHERE coordinator = 'u0012047' OR coordinator = 'u0015584' OR
  coordinator = 'u0024689' OR coordinator = 'u0031447'

Beetje omslachtig, niet? Dat kan dus korter met IN:

SELECT opocode, naam, coordinator
FROM opleidingsonderdeel
WHERE coordinator IN ('u0012047','u0015584','u0024689','u0031447')

Overzicht operatoren

Hieronder vind je een overzicht van de meest gebruikte operatoren in een WHERE clause.

Oefeningen SQLzoo

SQLzoo is een interactief oefenplatform. Je voert SQL-code in die het systeem voor jou nakijkt. Het is een goed idee om deze oefeningen te maken. Je leert immers werken met andere tabellen dan die die we in de cursustekst doorlopend gebruiken.

Tabel ‘world’

Deze tabel bevat volgende gegevens van landen:

• naam
• continent
• oppervlakte
• aantal inwoners
• bruto binnenlands product