Subqueries

En subquery er en spørring inne i en annen spørring.
En subquery kan brukes enten med WHERE eller HAVING.
En subquery bruker i tillegg til de vanlige operatorene =, <>, >, >=, < og <= også operatorene IN, NOT IN, EXISTS, NOT EXISTS, ANY/SOME og ALL.
En subquery må alltid stå på høyre side av en operator.
En subquery kan returnere en eller flere rader og kolonner, men det er kun subqueries som bruker EXISTS/NOT EXISTS som kan returnere flere kolonner.
En subquery kan inneholde en ny subqery, da kaller vi det en nested subquery.

EksempelDB: Kursregister

person

personid	navn
1	Per Olsen
2	Anne Evensen
3	Hans Jensen
4	Inger Karlsen
5	Hans Nilsen
6	Janne Elise Julsen

kurs

kursid	navn
1	Matematikk
2	Datakommunikasjon
3	Databaser
4	Matematikk II
5	Programmering for web
6	Engelsk fonetikk

elevKurs

kursid(FK->kurs)	blokk	elevid(FK->person)
1	1-02	1
1	1-02	2
1	1-03	4
2	2-02	2
2	4-03	3
2	4-03	4
3	6-03	3
4	5-03	4
5	2-03	2
5	2-03	4

laererKurs

kursid(FK->kurs)	blokk	laererid(FK->person)
1	1-02	5
1	1-03	1
2	2-02	6
2	4-03	6
3	6-03	1
4	5-03	5
5	2-03	6
5	6-04	NULL

Eksempler

Vanlige operatorer

Skriv ut blokk og navn på elever som har tatt kurset Datakommunikasjon

SELECT navn, blokk
FROM elevKurs ek, person p
WHERE kursid = (SELECT kursid
                FROM kurs
                WHERE navn='Datakommunikasjon')
AND ek.elevid = p.personid;

navn                 blokk 
-------------------- -------- 
Anne Evensen         H02
Hans Jensen          V03
Inger Karlsen        V03

(3 row(s) affected)

Skriv ut navnet på alle kurs høsten 2002 som ikke hadde Janne Elise Julsen som lærer

SELECT DISTINCT navn
FROM kurs k, elevKurs e
WHERE e.kursid <> (SELECT DISTINCT kursid
                   FROM laererKurs l, person p
                   WHERE navn = 'Janne Elise Julsen'
                   AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02')
                   AND l.laererid = p.personid)
AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02')
AND e.kursid = k.kursid;

navn                      
------------------------- 
Matematikk

(1 row(s) affected)

IN/NOT IN

Skriv ut navn på deltagere som har tatt kurset Matematikk(kursid = 1)

SELECT DISTINCT navn 
FROM person p 
WHERE personid IN (SELECT elevid
                  FROM elevKurs
                  WHERE kursid = 1);

navn                 
-------------------- 
Anne Evensen
Inger Karlsen
Per Olsen

(3 row(s) affected)

Skriv ut navn på deltagere som ikke har tatt kurset Matematikk(kursid = 1)

SELECT DISTINCT navn 
FROM person p 
WHERE personid NOT IN (SELECT elevid
                  FROM elevKurs
                  WHERE kursid = 1);

navn                 
-------------------- 
Hans Jensen
Hans Nilsen
Janne Elise Julsen

(3 row(s) affected)

ANY/SOME og ALL

Finn alle studenter som har flere kurs enn hvertfall en av elevene i kurset Matematikk

SELECT navn, COUNT(kursid) AS "Fulgt antall kurs"
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid
GROUP BY(navn)
HAVING COUNT(ek.kursid) > ANY(SELECT COUNT(kursid)
                              FROM elevKurs
                              WHERE elevid IN (SELECT elevid
                                               FROM elevKurs
                                               WHERE kursid = 1)
                              GROUP BY(elevid));

navn                 Fulgt antall kurs 
-------------------- ----------------- 
Anne Evensen         3
Hans Jensen          2
Inger Karlsen        4

(3 row(s) affected)

SOME og ANY er det samme, så dermed kan man få samme resultat ved å skrive:

SELECT navn, COUNT(kursid) AS "Fulgt antall kurs"
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid
GROUP BY(navn)
HAVING COUNT(ek.kursid) > SOME(SELECT COUNT(kursid)
                               FROM elevKurs
                               WHERE elevid IN (SELECT elevid
                                                FROM elevKurs
                                                WHERE kursid = 1)
                               GROUP BY(elevid));

navn                 Fulgt antall kurs 
-------------------- ----------------- 
Anne Evensen         3
Hans Jensen          2
Inger Karlsen        4

(3 row(s) affected)

Skriv ut navnet og antall kurs for den/de som har tatt flest kurs

SELECT navn, COUNT(kursid) AS "Fulgt antall kurs"
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid
GROUP BY(navn)
HAVING COUNT(ek.kursid) >= ALL(SELECT COUNT(kursid)
                               FROM elevKurs
                               GROUP BY(elevid));

navn                 Fulgt antall kurs 
-------------------- ----------------- 
Inger Karlsen        4

(1 row(s) affected)

EXISTS/NOT EXISTS

Skriv ut navn og kursnavn for lærere som også har vært elever

SELECT p.navn AS elev, k.navn AS kurs
FROM person p, elevKurs e, kurs k
WHERE EXISTS (SELECT *
              FROM laererKurs l
              WHERE l.laererid = e.elevid)
AND p.personid = e.elevid
AND k.kursid = e.kursid;

elev                 kurs                      
-------------------- ------------------------- 
Per Olsen            Matematikk

(1 row(s) affected)

Skriv ut kursnavn for kurs som ikke ble kjørt høsten 2002

SELECT navn
FROM kurs k
WHERE NOT EXISTS (SELECT *
                  FROM elevKurs e
                  WHERE e.kursid = k.kursid
                  AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02'));

navn                      
------------------------- 
Databaser
Matematikk II
Programmering for web
Engelsk fonetikk

(4 row(s) affected)

Fil med tabeller og data

Elin K. Ajer Andreassen, 2004