A Beginner’s Guide to the True Order of SQL Operations

De SQL taal is zeer intuïtief. Totdat het dat niet is.

In de loop der jaren hebben veel mensen de SQL taal om verschillende redenen bekritiseerd. Bijvoorbeeld: IDE’s kunnen niet gemakkelijk raden welke auto completion opties ze moeten aanbieden, want zolang je de FROM clause niet specificeert, zijn er (nog) geen tabellen in scope:

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

Deze dingen zijn raar, omdat de lexicale volgorde van operaties niet overeenkomt met de logische volgorde van operaties. Wij mensen kunnen dit verschil in volgorde soms (vaak) intuïtief begrijpen. We weten bijvoorbeeld dat we gaan selecteren uit de klantentabel. Maar de IDE weet dit niet.

GROUP BY draagt de meeste verwarring bij

Wanneer een junior ontwikkelaar / SQL-beginner met SQL begint te werken, zullen ze vrij snel achter aggregatie en GROUP BY komen. En ze zullen snel dingen schrijven als:

SELECT count(*)FROM customer

Ja, we hebben 200 klanten!

En dan:

SELECT count(*)FROM customerWHERE first_name = 'Steve'

Wauw, 90 van hen heten Steve! Interessant. Laten we eens kijken hoeveel we er per naam hebben…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Zeer leuk. Maar zijn ze allemaal hetzelfde? Laten we ook eens naar de achternaam kijken

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

Oeps!

ORA-00979: not a GROUP BY expression

Jemig, wat betekent dat? (let op, helaas, MySQL gebruikers die niet de STRICT modus gebruiken zullen hier nog steeds een resultaat krijgen met willekeurige achternamen!, dus een nieuwe MySQL gebruiker zal hun fout niet begrijpen)

Hoe leg je dit eenvoudig uit aan een SQL newbie? Voor “pro’s” lijkt het duidelijk, maar is het ook echt duidelijk? Is het duidelijk genoeg dat je het gemakkelijk aan een beginner kunt uitleggen? Denk er eens over na. Waarom is elk van deze verklaringen semantisch juist of onjuist?

Het probleem heeft te maken met de syntax

De SQL syntax werkt op een vergelijkbare manier als de Engelse taal. Het is een commando. We beginnen commando’s met werkwoorden. Het werkwoord is SELECT (of INSERT, UPDATE, DELETE, CREATE, DROP, enz. enz.)

Helaas is de menselijke taal ongelooflijk slecht geschikt voor de veel formelere wereld van het programmeren. Hoewel het enige troost biedt aan nieuwe gebruikers (mogelijk niet-programmeurs) die absolute beginners zijn, maakt het de dingen alleen maar moeilijk voor alle anderen. Alle verschillende SQL-clausules hebben zeer complexe onderlinge afhankelijkheden. Bijvoorbeeld:

• In aanwezigheid van een GROUP BY clausule, kunnen alleen expressies die zijn opgebouwd uit GROUP BY expressies (of functionele afhankelijkheden daarvan), of aggregate functies worden gebruikt in HAVING, SELECT, en ORDER BY clausules.
• Voor de eenvoud, laten we het zelfs niet hebben over GROUPING SETS
• In feite zijn er zelfs een paar gevallen waarin GROUP BY wordt geïmpliceerd. Bijv. als u een “naakte” HAVING clausule schrijft
• Een enkele aggregate functie in de SELECT clausule (bij afwezigheid van GROUP BY) zal aggregatie in een enkele rij afdwingen
• In feite kan dit ook worden geïmpliceerd door die aggregate functie in ORDER BY te zetten (om welke reden dan ook)
• U kunt ORDER BY heel wat expressies die verwijzen naar kolommen uit de FROM clausule zonder SELECT ze te gebruiken. Maar dat is niet meer waar als je SELECT DISTINCT
• …

schrijft De lijst is eindeloos. Als u geïnteresseerd bent, kunt u de SQL standaarddocumenten lezen en nagaan hoeveel rare en ingewikkelde onderlinge afhankelijkheden er bestaan tussen de vele clausules van het SELECT statement.

Kan dit ooit begrepen worden?

Gelukkig, ja! Er is een eenvoudige truc, die ik altijd uitleg aan de afgevaardigden die mijn SQL Masterclass bezoeken. De lexicale (syntactische) volgorde van SQL-bewerkingen (clausules) komt helemaal niet overeen met de logische volgorde van bewerkingen (hoewel ze dat soms toevallig wel doen). Dankzij moderne optimizers komt de volgorde ook niet overeen met de werkelijke volgorde van bewerkingen, zodat we eigenlijk hebben: syntactische -> logische -> werkelijke volgorde, maar dat laten we nu even buiten beschouwing.

De logische volgorde van bewerkingen is de volgende (voor de “eenvoud” laat ik verkoperspecifieke dingen als CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT en al het andere weg):

• FROM: Dit is eigenlijk het eerste dat gebeurt, logisch gezien. Voordat iets anders gebeurt, laden we alle rijen van alle tabellen en voegen ze samen. Voordat je schreeuwt en boos wordt: Nogmaals, dit is wat er eerst logischerwijs gebeurt, niet feitelijk. De optimiser zal zeer waarschijnlijk niet eerst deze operatie doen, dat zou dom zijn, maar een index openen op basis van de WHERE clausule. Maar nogmaals, logisch gezien gebeurt dit als eerste. Ook: alle JOIN clausules zijn eigenlijk onderdeel van deze FROM clausule. JOIN is een operator in de relationele algebra. Net zoals + en - operatoren zijn in de rekenkunde. Het is geen onafhankelijke clausule, zoals SELECT of FROM
• WHERE: Als we alle rijen uit de bovenstaande tabellen hebben geladen, kunnen we ze nu weer weggooien met WHERE
• GROUP BY: Als je wilt, kun je de rijen die overblijven na WHERE nemen en ze in groepen of emmers zetten, waarbij elke groep dezelfde waarde bevat voor de GROUP BY expressie (en alle andere rijen in een lijst voor die groep worden gezet). In Java zou je zoiets krijgen als: Map<String, List<Row>>. Als je wel een GROUP BY clausule specificeert, dan bevatten je feitelijke rijen alleen de groepskolommen, niet langer de overige kolommen, die nu in die lijst staan. Die kolommen in de lijst zijn alleen zichtbaar voor aggregaatfuncties die op die lijst kunnen werken. Zie hieronder.
• aggregaties: Dit is belangrijk om te begrijpen. Het maakt niet uit waar u uw aggregatiefunctie syntactisch plaatst (d.w.z. in de SELECT-clausule, of in de ORDER BY-clausule), dit hier is de stap waar aggregatiefuncties worden berekend. Direct na GROUP BY. (Onthoud: logisch. Slimme databases kunnen ze al eerder berekend hebben, eigenlijk). Dit verklaart waarom je een aggregate functie niet in de WHERE clausule kunt zetten, omdat de waarde ervan nog niet kan worden benaderd. De WHERE-clausule gebeurt logischerwijs vóór de aggregatiestap. Aggregatiefuncties hebben toegang tot kolommen die je in “deze lijst” hebt gezet voor elke groep, hierboven. Na de aggregatie zal “deze lijst” verdwijnen en niet langer beschikbaar zijn. Als je geen GROUP BY clausule hebt, zal er gewoon één grote groep zijn zonder enige sleutel, die alle rijen bevat.
• HAVING: … maar nu heb je toegang tot de waarden van de aggregatiefunctie. U kunt bijvoorbeeld controleren dat count(*) > 1 in de HAVING clausule. Omdat HAVING na GROUP BY komt (of GROUP BY impliceert), hebben we geen toegang meer tot kolommen of uitdrukkingen die geen GROUP BY-kolommen waren.
• WINDOW: Als u de geweldige vensterfunctie-functie gebruikt, is dit de stap waar ze allemaal worden berekend. Alleen nu. En het leuke is, omdat we alle aggregate functies al hebben berekend (logisch!), kunnen we aggregate functies nestelen in window functies. Het is dus perfect in orde om dingen te schrijven als sum(count(*)) OVER () of row_number() OVER (ORDER BY count(*)). Dat vensterfuncties nu pas logisch worden berekend, verklaart ook waarom je ze alleen in de SELECT of ORDER BY clausules kunt zetten. Ze zijn niet beschikbaar voor de WHERE clausule, wat voorheen wel het geval was. Merk op dat PostgreSQL en Sybase SQL Anywhere een echte WINDOW clausule hebben!
• SELECT: Eindelijk. We kunnen nu alle rijen gebruiken die uit de bovenstaande clausules voortkomen en er nieuwe rijen/tupels van maken met SELECT. We hebben toegang tot alle vensterfuncties die we hebben berekend, alle aggregaatfuncties die we hebben berekend, alle groeperingskolommen die we hebben opgegeven, of als we niet hebben gegroepeerd/geaggregeerd, kunnen we alle kolommen uit onze FROM-clausule gebruiken. Onthoud: Zelfs als het lijkt alsof we dingen aggregeren binnen SELECT, is dit allang gebeurd, en de zoete zoete count(*) functie is niets meer dan een verwijzing naar het resultaat.
• DISTINCT: Ja! DISTINCT gebeurt na SELECT, zelfs als het voor uw SELECT kolom lijst is gezet, syntaxis-wise. Maar denk er eens over na. Het is volkomen logisch. Hoe kunnen we anders afzonderlijke rijen verwijderen, als we nog niet alle rijen (en hun kolommen) kennen?
• UNION, INTERSECT, EXCEPT: Dit is een no-brainer. Een UNION is een operator die twee subqueries verbindt. Alles waar we het tot nu toe over hebben gehad was een subquery. De output van een union is een nieuwe query met dezelfde rijtypes (d.w.z. dezelfde kolommen) als de eerste subquery. Gewoonlijk. Want in wacko Oracle is de voorlaatste subquery de juiste om de kolomnaam te definiëren. Oracle database, de syntactische trol 😉
• ORDER BY: Het is volkomen logisch om de beslissing om een resultaat te ordenen uit te stellen tot het eind, omdat alle andere operaties hashmaps kunnen gebruiken, intern, dus elke tussenliggende ordening kan weer verloren gaan. Dus kunnen we nu het resultaat ordenen. Normaal gesproken kun je veel rijen benaderen vanuit de ORDER BY clausule, inclusief rijen (of expressies) die je niet SELECT hebt opgegeven. Maar wanneer je DISTINCT hebt opgegeven, kun je niet meer ordenen op rijen / expressies die niet geselecteerd waren. Waarom? Omdat de volgorde dan nogal ongedefinieerd zou zijn.
• OFFSET: Gebruik geen offset
• LIMIT, FETCH, TOP: Nu zetten verstandige databases de LIMIT (MySQL, PostgreSQL) of FETCH (DB2, Oracle 12c, SQL Server 2012) clausule helemaal aan het eind, syntactisch. Vroeger dachten Sybase en SQL Server dat het een goed idee zou zijn om TOP als een sleutelwoord in SELECT te hebben. Alsof de juiste volgorde van SELECT DISTINCT al niet verwarrend genoeg was.

Daar, we hebben het. Het is volkomen logisch. En als je ooit iets wilt doen dat niet in de “juiste volgorde” staat, is de eenvoudigste truc altijd je toevlucht te nemen tot een afgeleide tabel. Als je bijvoorbeeld wilt groeperen op een vensterfunctie:

Waarom werkt dat? Omdat:

• In de afgeleide tabel, FROM gebeurt eerst, en dan wordt de WINDOW berekend, dan wordt de emmer SELECTed.
• De buitenste SELECT kan nu het resultaat van deze vensterfunctie berekening behandelen als elke gewone tabel in de FROM clausule, dan GROUP BY een gewone kolom, dan aggregeren, dan SELECT

Laten we onze oorspronkelijke voorbeelden nog eens bekijken met een uitleg waarom ze werken of waarom ze niet werken.

Denk altijd aan de logische volgorde van bewerkingen

Als u niet vaak SQL schrijft, kan de syntax inderdaad verwarrend zijn. Vooral GROUP BY en aggregaties “besmetten” de rest van de hele SELECT clausule, en de dingen worden echt raar. Wanneer we geconfronteerd worden met deze vreemdheid, hebben we twee opties:

• Worden boos en schreeuwen tegen de ontwerpers van de SQL-taal
• Begrijpen ons lot, sluiten onze ogen, vergeten de snytax en onthouden de logische bewerkingsvolgorde

Ik raad over het algemeen het laatste aan, omdat de dingen dan een stuk logischer beginnen te worden, met inbegrip van de prachtige cumulatieve dagelijkse inkomstenberekening hieronder, die de dagelijkse inkomsten (SUM(amount) aggregatiefunctie) nestelt binnen de cumulatieve inkomsten (SUM(...) OVER (...) vensterfunctie):

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… omdat aggregaties logischerwijs vóór vensterfuncties komen.

Caveat: ORDER BY-clausule

Er zijn enkele caveats rond de ORDER BY-clausule, die tot verdere verwarring zouden kunnen bijdragen. Standaard wordt er verder van uitgegaan dat de logische volgorde van bewerkingen correct is. Maar dan zijn er enkele speciale gevallen, in het bijzonder:

• In de afwezigheid van een DISTINCT clausule
• In de afwezigheid van set operaties zoals UNION

U kunt expressies in ORDER BY refereren, die niet door SELECT zijn geprojecteerd. De volgende query is perfect in orde in de meeste databases:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Er is een “virtuele” / impliciete ACTOR_ID projectie, alsof we hadden geschreven:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Maar dan de ACTOR_ID kolom weer uit het resultaat hebben verwijderd. Dit is erg handig, hoewel het kan leiden tot enige verwarring over de semantiek en de volgorde van bewerkingen. In het bijzonder kunt u in een dergelijke situatie geen gebruik maken van bijvoorbeeld DISTINCT. De volgende query is ongeldig:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Want, wat als er twee actoren zijn met dezelfde naam maar met heel verschillende ID’s? De ordening zou nu ongedefinieerd zijn.

Met set operations is het nog duidelijker waarom dit niet is toegestaan:

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

In dit geval is de kolom ACTOR_ID niet aanwezig in de tabel CUSTOMER, dus de query heeft geen enkele zin.

Verder lezen

Wilt u meer weten? We hebben ook deze artikelen voor u om te lezen:

• SQL GROUP BY en functionele afhankelijkheden: Een zeer nuttige functie
• Hoe SQL GROUP BY had moeten worden ontworpen – Net als Neo4j’s Implicit GROUP BY
• Hoe SQL GROUP BY en Aggregaties te vertalen naar Java 8
• Begrijpt u SQL’s GROUP BY en HAVING clausules echt?
• GROUP BY ROLLUP / CUBE