6.4 SQL – avanserte spørringer og JOIN

Den virkelige kraften: å koble sammen

Så langt har vi jobbet med én tabell om gangen. Men den virkelige kraften i relasjonsdatabaser ligger i å koble sammen data fra flere tabeller. Vil du se elevnavn sammen med fagnavn og karakterer, ligger informasjonen spredt over tre tabeller. For å sette den sammen trenger du JOIN.

En JOIN kobler rader fra to tabeller basert på en felles kolonne. Den vanligste er INNER JOIN, som returnerer bare rader der det finnes en match i begge tabellene. I stedet for å se en klasse_id som et tall, kan du hente klassenavnet:

SELECT elever.fornavn, klasser.klassenavn
FROM elever
INNER JOIN klasser ON elever.klasse_id = klasser.klasse_id;

Delen etter ON forteller hvilke kolonner som skal matches. For å slippe lange tabellnavn bruker man ofte aliaser: FROM elever e INNER JOIN klasser k ON e.klasse_id = k.klasse_id, der e og k er kortnavn.

Det finnes flere JOIN-typer. INNER JOIN tar bare med rader som har match i begge tabellene. LEFT JOIN tar med alle rader fra venstre tabell, og fyller med NULL der det ikke finnes match til høyre. Har du 8 elever, men bare 6 har karakterer, gir en INNER JOIN mellom elever og karakterer 6 rader (bare de med karakter), mens en LEFT JOIN gir minst 8 (alle elever, også de uten karakter). RIGHT JOIN og FULL OUTER JOIN finnes også, men støttes ikke direkte i SQLite.

Å regne og gruppere

Nå kan vi begynne å analysere. Aggregeringsfunksjoner beregner én verdi ut fra mange rader. COUNT() teller rader, SUM() summerer, AVG() regner gjennomsnitt, og MIN()/MAX() finner laveste og høyeste verdi. For eksempel gir SELECT COUNT(*) FROM elever antall elever, og SELECT AVG(karakter) FROM karakterer gjennomsnittet. Bruk AS for å gi resultatkolonnen et forståelig navn (alias): SELECT AVG(karakter) AS gjennomsnitt.

Det virkelig nyttige skjer når du kombinerer aggregering med GROUP BY, som grupperer rader med like verdier slik at funksjonen beregnes per gruppe. Vil du telle elever per klasse:

SELECT klasse_id, COUNT(*) AS antall_elever
FROM elever
GROUP BY klasse_id;

Dette gir én rad per klasse med antallet. På samme måte kan du finne gjennomsnittskarakter per fag ved å JOIN-e karakterer med fag og gruppere på fagnavn.

Men hva om du bare vil se gruppene som oppfyller en betingelse? Da bruker du HAVING. Her er en viktig forskjell: WHERE filtrerer individuelle rader før grupperingen, mens HAVING filtrerer hele grupper etter grupperingen. Vil du finne fag med snitt over 4, bruker du GROUP BY f.fagnavn HAVING AVG(k.karakter) > 4. Du kan til og med kombinere dem: WHERE filtrerer for eksempel ut bare H2024-karakterer først, så grupperer GROUP BY etter fag, og til slutt fjerner HAVING grupper med for lavt snitt.

Spørringer inne i spørringer

Noen ganger trenger du svaret fra én spørring for å stille en annen. Da bruker du en subquery (underspørring) – en SELECT-setning nestet inne i en annen. Underspørringen kjøres først, og resultatet brukes av den ytre.

Den vanligste plasseringen er i WHERE. Vil du finne alle karakterer over gjennomsnittet, regner underspørringen ut snittet, og den ytre spørringen filtrerer mot det:

SELECT e.fornavn, k.karakter
FROM karakterer k
INNER JOIN elever e ON k.elev_id = e.elev_id
WHERE k.karakter > (SELECT AVG(karakter) FROM karakterer);

Du kan også bruke subquery med IN. For å finne alle elever som har karakter 6 i et eller annet fag, finner underspørringen alle elev_id-er med karakter 6, og den ytre henter navnene: WHERE elev_id IN (SELECT elev_id FROM karakterer WHERE karakter = 6).

En tredje variant er subquery i FROM, der underspørringen lager en midlertidig tabell (en avledet tabell). Du kan for eksempel lage en tabell med gjennomsnitt per elev, gi den et alias, og så filtrere på snittet i den ytre spørringen. Med JOIN, aggregering, gruppering og underspørringer er SQL ikke lenger bare et lagringsspråk, men et fullverdig analyseverktøy som kan svare på spørsmål som «hvilken klasse har flest elever med toppkarakter?».

Oppsummering

Dette kapittelet gjorde SQL til et analyseverktøy. JOIN kobler tabeller på en felles kolonne – INNER JOIN tar bare med rader som matcher i begge, mens LEFT JOIN tar med alle fra venstre tabell. Aggregeringsfunksjoner (COUNT, SUM, AVG, MIN, MAX) beregner én verdi av mange rader, og blir kraftige sammen med GROUP BY, som regner per gruppe.

Forskjellen mellom WHERE (filtrerer rader før gruppering) og HAVING (filtrerer grupper etterpå) er sentral. Og subqueries – spørringer inne i spørringer – lar deg svare på sammensatte spørsmål, som å finne alle karakterer over gjennomsnittet. Med disse verktøyene kan du trekke ut innsikt fra data spredt over mange tabeller.