SQL — joins og subqueries

Skjul kartesian-produktet bak en intensjon

Tradisjonelt SQL kombinerer tabeller med FROM r, s WHERE r.k = s.k. Det fungerer, men det blander join-betingelsen sammen med radfilter-betingelsen i WHERE. JOIN ... ON skiller dem og gjør spørringen mer lesbar — og er nødvendig for ytre joins.

SELECT i.name, t.course_id
FROM instructor i, teaches t
WHERE i.ID = t.ID
  AND i.dept_name = 'CS';

SELECT i.name, t.course_id
FROM instructor i
INNER JOIN teaches t ON i.ID = t.ID
WHERE i.dept_name = 'CS';

Begge gir samme resultat. Den nederste skiller hva som er en match-betingelse (i ON) fra et radfilter (i WHERE). Det blir kritisk for outer joins.

Fire typer JOIN, fire ulike svar

Konkret eksempel

Tabellen instructor har 3 ansatte; teaches har 4 oppføringer der én ansatt mangler i instructor:

Tre måter å skrive match-betingelsen på

ON — vilkårlig predikat

Mest fleksibel. Kolonnene fra begge tabellene blir med i resultatet:

SELECT *
FROM course c JOIN department d
  ON c.dept_name = d.dept_name AND d.budget > 1000000;

USING(col) — match på navngitt kolonne

Kort form når kolonnenavnene er like. Kolonnen som matches dukker opp én gang i resultatet — ikke to.

SELECT *
FROM course JOIN department USING (dept_name);

NATURAL JOIN — implisitt USING på alle felles kolonner

Kompakt — og farlig. SQL leter etter alle kolonner som har samme navn, og bruker dem alle som match. En liten skjemaendring kan stille endre semantikken.

SELECT *
FROM course NATURAL JOIN department;
-- Hvis noen senere legger til en kolonne med samme navn
-- i begge tabeller, blir spørringen subtilt feil.

Når en tabell må joines mot seg selv

Klassisk eksempel: hierarkier. employee(id, name, manager_id), der manager_id peker på en annen rad i samme tabell.

-- Liste opp alle ansatte sammen med navnet på lederen sin
SELECT e.name AS ansatt,
       m.name AS leder
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;

Aliaser må brukes — uten dem ville id være tvetydig. Tenk på e og m som to «kopier» av samme tabell.

Et annet eksempel: «Lønninger som er høyere enn minst én Biology-ansatt»

SELECT DISTINCT T.name
FROM instructor T, instructor S
WHERE T.salary > S.salary
  AND S.dept_name = 'Biology';

Dette er den klassiske formuleringen i Silberschatz. DISTINCT er nødvendig fordi T kan matche flere S-rader.

Spørring inni en spørring

En subquery er en SELECT skrevet inne i en annen spørring. Den kan stå i WHERE, FROM, eller SELECT.

1. Skalar-subquery — én verdi

-- Snittlønn over alle
SELECT name, salary
FROM instructor
WHERE salary > (SELECT AVG(salary) FROM instructor);

2. Tabell-subquery i FROM (derived table)

SELECT dept_name, max_salary
FROM (
  SELECT dept_name, MAX(salary) AS max_salary
  FROM instructor
  GROUP BY dept_name
) AS per_dept
WHERE max_salary > 90000;

3. Subquery i SELECT (inline)

SELECT dept_name,
       (SELECT AVG(salary)
        FROM instructor i
        WHERE i.dept_name = d.dept_name) AS snitt
FROM department d;

Subqueryen kjører én gang per rad i den ytre spørringen — det gjør den korrelert. Mer om det rett under.

Operatorer på subquery-resultater

Tre formuleringer av samme spørsmål

«Finn navn på ansatte som underviser i minst ett emne»:

SELECT name
FROM instructor
WHERE ID IN
  (SELECT ID FROM teaches);

SELECT name
FROM instructor i
WHERE EXISTS (
  SELECT 1 FROM teaches t
  WHERE t.ID = i.ID);

-- Trygg variant: ansatte som IKKE underviser noe
SELECT name
FROM instructor i
WHERE NOT EXISTS (
  SELECT 1 FROM teaches t WHERE t.ID = i.ID
);

Når den indre refererer til den ytre

En korrelert subquery refererer til en kolonne fra den ytre spørringen. Det betyr (logisk) at den må evalueres på nytt for hver ytre rad.

-- For hver avdeling: ansatte med over-snittlig lønn for deres avdeling
SELECT name, dept_name, salary
FROM instructor i
WHERE salary > (
  SELECT AVG(salary)
  FROM instructor i2
  WHERE i2.dept_name = i.dept_name   -- ← korrelasjon
);

Optimalisatoren prøver ofte å skrive om dette til en join + GROUP BY for ytelse, men logisk modell er «for hver rad i, regn ut en avdelingssnitt og sammenlign».

Klassisk: division med NOT EXISTS NOT EXISTS

Spørsmålet «hvilke studenter har tatt alle emner i CS-avdelingen?» kan ikke uttrykkes med vanlig join. Den klassiske SQL-løsningen er dobbel negasjon:

SELECT s.ID
FROM student s
WHERE NOT EXISTS (
  SELECT c.course_id
  FROM course c
  WHERE c.dept_name = 'CS'
    AND NOT EXISTS (
      SELECT 1
      FROM takes t
      WHERE t.ID = s.ID
        AND t.course_id = c.course_id
    )
);

Lest naturlig: «det finnes ikke et CS-emne som studenten ikke har tatt». Dobbel negasjon gir for-alle-kvantor-en SQL ikke har som primitiv.

Common Table Expressions

En WITH-klausul lar deg navngi en subquery og bruke den senere — som en lokal view. Bra for lesbarhet og når samme delspørring brukes flere ganger.

WITH dept_avg AS (
  SELECT dept_name, AVG(salary) AS snitt
  FROM instructor
  GROUP BY dept_name
)
SELECT i.name, i.dept_name, i.salary, d.snitt
FROM instructor i
JOIN dept_avg d USING (dept_name)
WHERE i.salary > d.snitt;

Du kan også definere flere CTE-er etter hverandre, og en CTE kan referere til en tidligere CTE i samme WITH.

Det du skal kunne

1. Velge riktig JOIN-type ut fra hvilke rader som må bevares.
2. Forklare hva LEFT JOIN ON … WHERE …IS NULL gjør (anti-join).
3. Skille ON, USING og NATURAL — og vite hvorfor NATURAL er skjør.
4. Skrive en self-join og forklare hvorfor aliaser er obligatoriske.
5. Velge mellom IN, EXISTS, ANY, ALL — og kjenne NOT IN-fella med NULL.
6. Identifisere en korrelert subquery og argumentere for når den kan skrives om til join.
7. Bruke WITH til å gjøre komplekse spørringer leselige.

Klar for views, transaksjoner og integritet? Gå til 3C →