5.2 Avansert SQL

Når data ligger spredt

Du kan allerede SELECT, WHERE og ORDER BY. Men i en normalisert database ligger data spredt over mange tabeller – det var jo hele poenget med normaliseringen. Skal du hente ut meningsfull informasjon, må du kombinere tabellene igjen. Det gjør du med JOIN.

Den vanligste varianten er INNER JOIN, som bare returnerer rader der det finnes et treff i begge tabeller. Vil du hente alle bøker sammen med forfatternavnet, kobler du Bok og Forfatter på forfatterID:

SELECT Bok.tittel, Forfatter.navn
FROM Bok
INNER JOIN Forfatter ON Bok.forfatterID = Forfatter.forfatterID;

Noen ganger vil du ha med rader uten treff. Da bruker du LEFT JOIN, som returnerer alle rader fra venstre tabell, også de uten match – de får bare NULL i kolonnene fra høyre. Vil du ha med forfattere som ennå ikke har skrevet noen bok, er LEFT JOIN svaret. I SQLite finnes ikke RIGHT JOIN eller FULL OUTER JOIN, men du kommer langt med å bytte rekkefølgen i en LEFT JOIN. Og du kan kjede flere JOIN-er sammen for å koble tre eller fire tabeller i én spørring.

Gruppering, aggregering og underspørringer

Ofte vil du ikke se hver enkelt rad, men et sammendrag. Da bruker du GROUP BY sammen med aggregatfunksjoner: COUNT() teller, SUM() summerer, AVG() regner gjennomsnitt, og MIN()/MAX() finner ytterpunkter. Vil du vite hvor mange elever som er i hver klasse, grupperer du på klasseID og teller. Vil du filtrere selve gruppene – for eksempel bare vise fag med snittkarakter over 4,0 – bruker du HAVING. Her er forskjellen viktig: WHERE filtrerer rader før gruppering, mens HAVING filtrerer grupper etter gruppering.

Rekkefølgen SQL kjører i, er verdt å huske: FROM velger tabell, WHERE filtrerer rader, GROUP BY grupperer, HAVING filtrerer grupper, SELECT velger kolonner, og ORDER BY sorterer til slutt. En GROUP BY må inkludere alle SELECT-kolonner som ikke er aggregatfunksjoner.

Noen ganger trenger du resultatet av én spørring inni en annen. Det kalles en subquery (underspørring). Du kan bruke den i WHERE – for eksempel for å finne elever med snitt høyere enn det totale gjennomsnittet ved å sammenligne mot (SELECT AVG(karakter) FROM Karakter). Du kan bruke den i FROM som om resultatet var en midlertidig tabell. Og du kan kombinere den med IN for å finne alle elever som finnes i en annen spørrings resultat, som de som har fått 6 i minst ett fag.

Views og indekser

Noen spørringer blir lange og kompliserte, og du skriver dem om og om igjen. Da kan en view hjelpe – en lagret SQL-spørring som oppfører seg som en tabell. Den lagrer ingen data, men gir deg en ferdig spørring til gjenbruk:

CREATE VIEW ElevOversikt AS
SELECT Elev.navn, Klasse.klassenavn, AVG(Karakter.karakter) AS snittkarakter
FROM Elev
JOIN Klasse ON Elev.klasseID = Klasse.klasseID
LEFT JOIN Karakter ON Elev.elevID = Karakter.elevID
GROUP BY Elev.elevID;

Etterpå kan du bruke SELECT * FROM ElevOversikt WHERE snittkarakter > 4.5 som om det var en vanlig tabell. Views forenkler komplekse spørringer, gir gjenbrukbar kode, skjuler kompleksitet for sluttbrukere, og kan brukes til sikkerhet ved å gi tilgang til viewet i stedet for rådatatabellen.

Når databasen vokser, kan spørringer bli trege. En indeks er en datastruktur som gjør søk raskere – tenk på stikkordregisteret bak i en bok, som lar deg finne et tema uten å bla gjennom alt. Du bør lage indekser på kolonner som ofte brukes i WHERE og JOIN, og på fremmednøkler; primærnøkler får automatisk indeks. Men det er en avveining: indekser gjør SELECT dramatisk raskere, men de tar ekstra plass og gjør INSERT, UPDATE og DELETE litt tregere fordi indeksen må oppdateres. Derfor lager du ikke indeks på alt – bare på kolonner som faktisk brukes mye i spørringer.

Oppsummering

Når data ligger spredt over normaliserte tabeller, samler vi dem igjen med JOIN – INNER JOIN for kun treff, LEFT JOIN for å beholde alt fra venstre tabell. GROUP BY med aggregatfunksjoner som COUNT, SUM og AVG gir sammendrag, og HAVING filtrerer gruppene etter at WHERE har filtrert radene. Subqueries lar oss bruke resultatet av én spørring inni en annen.

For å rydde og gjenbruke lager vi views – lagrede spørringer som oppfører seg som tabeller. Og for fart bruker vi indekser, som gjør SELECT og JOIN raskere mot prisen av litt tregere skriving og mer plass. Med disse verktøyene kan du hente ut nesten hvilken som helst innsikt fra en relasjonsdatabase. I neste kapittel kobler vi Python til SQLite.